YII CActiveDataProvider with relational tables

Question

I have three tables:

• Content (id)
• ContentCategory (id_content, id_category)
• Category (id)

Content Relations

'Categories' => array(self::MANY_MANY, 'Category', 'ContentCategory(id_content, id_category)'), 'category' => array(self::HAS_MANY, 'Category', 'id'), 

I need to get all Content entries that have a specific category (in CActiveDataProvider for use in CListView).

When I use findAll (), I get the records I want (works),

 $model=Content::model()->with(array( 'Categorieses'=>array( 'condition'=>'id_category=1', ), ))->findAll(); 

But when I work with CActiveDataProvider, I get all the entries in the Content (not those that have a certain category - it does not work)

 $dataProvider=new CActiveDataProvider('Content', array( 'pagination'=>array('pageSize'=>15), 'criteria'=>array( 'with'=>array( 'Categories'=>array( 'condition'=>'id_category=1', ), ), ), ) ); 

How can i do this?

Thanks a lot!

Answer 1

When tables are associated with MANY_MANY or HAS_MANY, Yii can sometimes split one query into two SQL statements. I believe this is for efficiency, but it can ruin something like what you are trying to do, since the category query is in a different SQL expression than the Contact query.

The solution is to use the lesser known CDbCriteria property called together . If you set this to true, this will force the query to select from both tables in the same SQL statement. Then your condition will be effective.

If you always want this, add it to the relation:

 Categories' => array(self::MANY_MANY, 'Category', 'ContentCategory(id_content, id_category)','together'=>true), 

If you want it only in the case of the $dataProvider above, add it to parameters like this:

 'criteria'=>array( 'with'=>array( 'Categories'=>array( 'condition'=>'id_category=1' ), ), 'together'=>true, ), 

Answer 2

Relational Query Using