YII CActiveDataProvider with relational tables?

When tables are related MANY_MANY or HAS_MANY, Yii can sometimes break a single query into two SQL statements. I believe this is for efficiency, but it can mess up something like you are trying to do, since the Categories query is happening in a different SQL statement than the Contact query.

Up vote 0 down vote favorite share g+ share fb share tw.

I have three tables: Content (id) ContentCategory (id_content, id_category) Category (id) Relations of Content, 'Categories' => array(self::MANY_MANY, 'Category', 'ContentCategory(id_content, id_category)'), 'category' => array(self::HAS_MANY, 'Category', 'id'), I need to fetch all the records of Content that have some specific Category (in a CActiveDataProvider to use in CListView). When I use the findAll(), I get the records I want (it works), $model=Content::model()->with(array( 'Categorieses'=>array( 'condition'=>'id_category=1', ), ))->findAll(); But when I do with CActiveDataProvider I get all the records in Content (not the ones that have specific Category - Not works) $dataProvider=new CActiveDataProvider('Content', array( 'pagination'=>array('pageSize'=>15), 'criteria'=>array( 'with'=>array( 'Categories'=>array( 'condition'=>'id_category=1', ), ), ), ) ); How can I do that? Thanks a lot!

Yii link|improve this question asked Dec 6 '11 at 15:42Calete1.

When tables are related MANY_MANY or HAS_MANY, Yii can sometimes break a single query into two SQL statements. I believe this is for efficiency, but it can mess up something like you are trying to do, since the Categories query is happening in a different SQL statement than the Contact query. The solution is to use a lesser-known property of CDbCriteria called together.

If you set this to true, it will force the query to select from both tables in the same SQL statement. The condition you have will then be effective. If you always want this to happen, add it to the relation: Categories' => array(self::MANY_MANY, 'Category', 'ContentCategory(id_content, id_category)','together'=>true), If you want it just in the $dataProvider case above, add it to the parameters like this: 'criteria'=>array( 'with'=>array( 'Categories'=>array( 'condition'=>'id_category=1' ), ), 'together'=>true, ).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.