Activerecord With() And A Condition

Yii 1.1 General Discussion
1

I am using the with() method of AR to eagerly load related models.

The simple format


Post::model()->with('comments')->findAll();

works fine: it retrieves all posts and all their comments, even posts that have no comments.

Yet the more complicated format that puts a condition on the related models, does not behave as expected (in my opinion):


Post::model()->with(array(

    'comments'=>array('condition'=>'approved=1'),

))->findAll();

This last statement excludes a post that has either no comments or only unapproved comments.

But that is not what you would expect: you would expect it retrieves all posts + the approved comments.

This happens because Yii puts the condition in the where-clause of the query, instead of the left join part.

A workaround is to write the condition like this:


Post::model()->with(array(

    'comments'=>array('condition'=>'ifnull(approved,1)=1'),

))->findAll();

But that is less efficient, possibly incorrect if the field approved is allowed to be null and less portable (ifnull is not standard across all databases).

2

The Definitive Guide to Yii > Relational Active Record > Relational Query Options

So use ‘on’ instead of ‘condition’.

3

check this


Post::model()->with(array(

    'joinType'=>'LEFT OUTER JOIN',

    'comments'=>array('condition'=>'approved=1'),

))->findAll();
4

@Tsunami: thank you! The documentation in The Guide is more complete than in the Class Reference. I often forget to check it.

@KonApaz: well… that is exactly what is wrong with the example in the Class Reference, no? It would be better if there was an ‘on’ and not a ‘condition’ there.