Access Multiple Aggregate Functions In Cdbcriteria

Yii 1.1 General Discussion
1

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 = 't.id';

    $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?

Thanks!

2

I think you can use Statistical Query, put the aggregation query in the visitor model’s relations. Check this guide: http://www.yiiframework.com/doc/guide/1.1/en/database.arr#statistical-query

3

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.

4

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()

              ->with(array('visits'=>array('on'=>"visits.visitor_key='".$this->visitor_key."'")))

              ->find();

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

5

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.

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview

6

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.

7

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.