Yii Framework Forum: Activerecord With() And A Condition - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Activerecord With() And A Condition the condition goes to the where instead of the left join Rate Topic: -----

#1 User is offline   tomvdp 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 63
  • Joined: 06-November 12

Posted 10 June 2013 - 05:32 AM

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).
0

#2 User is offline   Tsunami 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 150
  • Joined: 16-February 12

Posted 10 June 2013 - 06:58 AM

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

Quote

condition: the WHERE clause.
on: the ON clause.

So use 'on' instead of 'condition'.
0

#3 User is offline   Kostas Apazidis (KonApaz) 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,335
  • Joined: 21-February 11
  • Location:Greece

Posted 10 June 2013 - 07:00 AM

check this

Post::model()->with(array(
    'joinType'=>'LEFT OUTER JOIN',
    'comments'=>array('condition'=>'approved=1'),
))->findAll();

Yii is the best php framework in the world!
It is also powerful and flexible for large scale websites
find our demo Yii extension on www.webkit.gr
Is it post useful? please v++ ;)
0

#4 User is offline   tomvdp 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 63
  • Joined: 06-November 12

Posted 10 June 2013 - 07:54 AM

@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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users