Problem with CDbCriteria and relational data

I’m trying to add a criteria for relation record.

here is my code




        $criteria=new CDbCriteria(array(

            'condition' => 't.status = '.Publication::STATUS_PUBLISHED,

            'order' => 't.create_time DESC',

            'together' => true,

            'with' => array(

                'category' => array(

                    'condition' => 'category.status = '.Category::STATUS_ACTIVE,

                    

                ), 'category.community'

            )

        ));


        if(isset($_GET['blog']))

            $criteria->addSearchCondition('community.adress', $_GET['blog']);

        else

            $criteria->addSearchCondition('onmain', true);


        $dataProvider=new CActiveDataProvider('Publication', array(

            'pagination'=>array(

                'pageSize'=> 5,

            ),

            'criteria'=>$criteria,

        ));



i use CListView to show records. But i get sql error

the "COUNT" query is ok




SELECT COUNT(*) FROM `publications` `t` LEFT OUTER JOIN `publications_has_categories` `category_category` ON (`t`.`id`=`category_category`.`publication_id`) LEFT OUTER JOIN `categories` `category` ON (`category`.`id`=`category_category`.`category_id`) LEFT OUTER JOIN `communities` `community` ON (`category`.`community_id`=`community`.`id`) WHERE ((t.status = 1) AND (community.adress = 'test')) AND (category.status = 1)



it has all joins, but the “data” query doesn’t




SELECT `t`.`id` AS `t0_c0`, `t`.`author_id` AS `t0_c1`, `t`.`title` AS `t0_c2`, `t`.`url` AS `t0_c3`, `t`.`description` AS `t0_c4`, `t`.`short_content` AS `t0_c5`, `t`.`full_content` AS `t0_c6`, `t`.`create_time` AS `t0_c7`, `t`.`update_time` AS `t0_c8`, `t`.`reads` AS `t0_c9`, `t`.`comments` AS `t0_c10`, `t`.`status` AS `t0_c11`, `t`.`onmain` AS `t0_c12` FROM `publications` `t` WHERE ((t.status = 1) AND (community.adress = 'test')) ORDER BY t.create_time DESC LIMIT 5



if i remove addSearchCondition, it makes 3 queries.

first query is with count, second is with limit, and third is data. Why it makes 3 queries? why pagination is separate query?

You should specify to do the with ‘togheter’ in order to retrive all in one query.

Like that you should see 2 queries, one with count and one with limit. That is done for retrive the totalItemCount (by counting) and then select only the needed items from the table (limit).

I already put it in CDbCriteria




...

'together' => true,

...



Sorry, I didn’t noticed.

There is a similar bug in CActiveRecord hereand here, maybe is related.

Often happens that count and select have different behaviour, and this, in my opinion, is a bug by himself. I think that this two query should always be identical except for count and limit, but often it doesn’t happen.

I can’t find any mistake in your code, maybe is a bug. You can post in bug report.

forget… i’ve found issue http://code.google.com/p/yii/issues/detail?id=1078

maybe qiang will fix it.