Access Multiple Aggregate Functions In Cdbcriteria

I’m having difficulty access the return values of my MySQL query.

    $criteria = new CDbCriteria;

    $criteria->select = "SUM(page_view_count) AS pageViewCount, SUM(conversion_count) as conversionCount, COUNT(*) AS visitCount, id";

    $criteria->condition = "t.visitor_key = '".$this->visitor_key."'";

    $criteria->group = '';

    $criteria->with = 'visitor';

    $result = Visit::model()->find($criteria);

    $visitCount = $result->visitCount; // error message "Property "Visit.visitCount" is not defined"

    //$visitCount = $result[0]->visitCount; // error message ""Property "Visit.0" is not defined""

Am I accessing the values correctly, or should I be using another method/object to run this query?


I think you can use Statistical Query, put the aggregation query in the visitor model’s relations. Check this guide:

Yes, I worked through that. My issue is that the ‘visitor_key’ is not a PK or FK and using the STAT relation has not really helped. I thought I would simply access the result columns of the SQL query but not certain how to do that.

Hmm… the easiest way is to add the properties ($pageViewCount, $conversionCount etc) to the model. But it’s quite weird solution I think :rolleyes:

Another way that I thought is to add the statistical query (if you can relate those 2 models), and about the ‘visitor_key’ we will put it in the criteria:

in the Visitor model:

public function relations()


  return array(

    'visits' => ... // you may already have this relation

    'pageViewCount' => array(self::STAT, 'Visit', 'visitor_id', 'select' => 'SUM(page_view_count)'),


in the controller:

    $result = Visitor::model()



Maybe there is a better solution, let’s see what others respond.

Hi G!! and explorer,

I think defining the public variable named "visitCount" in the model class should solve the problem, just as explorer has suggested.

class Visit extends CActiveRecord



    public $visitCount;


Then Yii will use it as the place to store the value when you select some value as ‘visitCount’,

I don’t think it’s very weird. It’s simple and clear.

And IMHO, STAT relation is not very convenient because you can not filter and/or sort by it.

If I may add, using STAT relations were the cleanest solution. Adding [font=“Courier New”]pageViewCount[/font] and [font=“Courier New”]conversionCount[/font] properties to the model feels a lot like abuse to me. If you absoluteley have to fetch all data in one query, I’d suggest going through DAO instead.

We opted to add a foreign key relationship and then use self::STAT for the count & sum values.

'visitCount'=>array( self::STAT, 'Visit', 'visitor_id' ),

'viewCount'=>array( self::STAT, 'Visit', 'visitor_id', 'select' => 'SUM(page_view_count)', ),

Worked like a charm, very clean!

Thank you for all the ideas.