YII CActiveDataProvider with relational tables

Question

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!

Answer 1

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