using count(...) in criteria->select

Archives Yii 1.0.x Discussions
1

Hi all.

There is one co-related topic here (stat relation):

http://www.yiiframew…php/topic,1418

But I need something other. Actually I have the following code:

<?php


        $criteria = new CDbCriteria();


        $criteria->select = array('id', 'name', 'COUNT(p.user_id) as votes', 'AVG(p.rating) as rating');


        $criteria->order = Yii::app()->params['tables']['post'] . '.date';


        $criteria->limit = self::SHORTLIST_SIZE;


        $criteria->join = 'LEFT JOIN ' . Yii::app()->params['tables']['post_rating'] . ' p ON p.post_id = id';


        $criteria->group = 'id';


        $current = Post::model()->with(array(


            'post_info' => array('select' => 'preview_image'),


        ))->findAll($criteria);

What is interesting, that the result query in ActiveFinder is what I need. But there Model object has loses COUNT and AVG attributes. This is sad to me.

Could someone explain me "how to get COUNT and AVG using criteria"? Or I have to use CDBCommand class instead?

2

One little addition.

If self::STAT relation may do what I need using no additional queries (just one more join), please suggest me how can I use it this way.

3

Did you try to use CDbExp​ression object instead of strings in your select?

4

Didn't. However, I've just solved this problem.

I've noticed, that COUNT and AVG attributes wasn't passed through the CJoinElement->populateRecord().

Actually, it reminded me, that additional attributes should be declared separately!!

I have just added:

<?php


    public $rating;


    public $votes;

in target model class.

And then it works fine.

2 kvl:

I’ll check for CDbExp​ression. Hope it is as nice as other ORM classes :)