1.1.3 - updated AR and new bugs now?!

Well…at ver. 1.1.3 my old code brokes and now doesn’t work. Bug is with relational AR with scope. How it was used:


$requests = Request::model()->scNotDeletedI()->with('customer:visibleCustomer')->findAll();

scNotDeletedI - common scope of Request that adds: t.status<>‘deleted’

visibleCustomer - common scope of Customer that adds: customer.tags_options LIKE ‘%public%’

Problem is that when i get $requests, I have ‘customer’ only for first request. Rest requests have NULL customer.

At 1.1.2 my SQL was expanded into:

SELECT t.id AS t0_c0, t.external_id AS t0_c1, t.request_group_id AS t0_c2, t.cid AS t0_c3, t.external_source_id AS t0_c4, t.partNumber AS t0_c5, t.qty AS t0_c6, t.case AS t0_c7, t.manufacturer AS t0_c8, t.targetPrice AS t0_c9, t.targetPrice_currency AS t0_c10, t.created AS t0_c11, t.modified AS t0_c12, t.leadTime AS t0_c13, t.deadLine AS t0_c14, t.notes AS t0_c15, t.status AS t0_c16, t.external_info AS t0_c17, t.tags AS t0_c18, customer.id AS t1_c0, customer.login AS t1_c1, customer.password AS t1_c2, customer.role AS t1_c3, customer.name AS t1_c4, customer.created AS t1_c5, customer.modified AS t1_c6, customer.lastLoginTime AS t1_c7, customer.country AS t1_c8, customer.city AS t1_c9, customer.crm_suppler_id AS t1_c10, customer.user_group_id AS t1_c11, customer.timezone AS t1_c12, customer.currency_commercial AS t1_c13, customer.currency_order AS t1_c14, customer.default_leadtime AS t1_c15, customer.email AS t1_c16, customer.allow_notifications AS t1_c17, customer.language AS t1_c18, customer.tags_options AS t1_c19, customer.tags_rule AS t1_c20 FROM request t LEFT OUTER JOIN users customer ON (t.cid=customer.id) WHERE (t.status<>‘deleted’) AND (customer.tags_options LIKE ‘%public%’) ORDER BY t.created DESC LIMIT 10

At 1.1.3 my SQL is expanding into:

SELECT t.id AS t0_c0, t.external_id AS t0_c1, t.request_group_id AS t0_c2, t.cid AS t0_c3, t.external_source_id AS t0_c4, t.partNumber AS t0_c5, t.qty AS t0_c6, t.case AS t0_c7, t.manufacturer AS t0_c8, t.targetPrice AS t0_c9, t.targetPrice_currency AS t0_c10, t.created AS t0_c11, t.modified AS t0_c12, t.leadTime AS t0_c13, t.deadLine AS t0_c14, t.notes AS t0_c15, t.status AS t0_c16, t.external_info AS t0_c17, t.tags AS t0_c18, customer.id AS t1_c0, customer.login AS t1_c1, customer.password AS t1_c2, customer.role AS t1_c3, customer.name AS t1_c4, customer.created AS t1_c5, customer.modified AS t1_c6, customer.lastLoginTime AS t1_c7, customer.country AS t1_c8, customer.city AS t1_c9, customer.crm_suppler_id AS t1_c10, customer.user_group_id AS t1_c11, customer.timezone AS t1_c12, customer.currency_commercial AS t1_c13, customer.currency_order AS t1_c14, customer.default_leadtime AS t1_c15, customer.email AS t1_c16, customer.allow_notifications AS t1_c17, customer.language AS t1_c18, customer.tags_options AS t1_c19, customer.tags_rule AS t1_c20 FROM request t LEFT OUTER JOIN users customer ON (t.cid=customer.id) AND (customer.tags_options LIKE ‘%public%’) WHERE (t.status<>‘deleted’) ORDER BY t.created DESC LIMIT 10

For me new version is OK too, but fact is, that only first record has customer, rest - nulls :(

Hi,

I am having this exact problem. I have a ‘posts’ table that has many ‘comments’ and I want to load all posts along with the comments that are approved. Using 1.1.3 the condition that is checking for approved comments is expanding in the WHERE clause instead of the JOIN claue and the result is that there are ‘posts’ entries that are not fetched at all they have no comments or no approved comments.

This is how the previous way - which supposedly worked in previous yii versions (condition inside JOIN):

LEFT OUTER JOIN “shop_dev”.“post_comment” “comments” [u][color="#FF0000"]ON [/color]/u [color="#FF0000"]AND (comments.is_published=‘1’)[/color] WHERE (post.is_published=‘1’)

This is the snippet that 1.1.3 generates (condition inside WHERE):

LEFT OUTER JOIN “shop_dev”.“post_comment” “comments” ON (“comments”.“post_id”=“post”.“id”) [u][color="#FF0000"]WHERE [/color]/u [color="#FF0000"]AND (comments.is_published=‘1’)[/color]

Why this change? It doesn’t look alright to me because adding a condition on a child inside ‘with()’ statement affects the parent model.

Thanks.

This is the correct behavoir. LEFT JOINs never should limit the number of distinct main table rows. See here:

http://www.yiiframework.com/forum/index.php?/topic/10185-using-relations-and-conditions/

You could use a INNER JOIN instead, if you want that.

Ah I see, so if I put the condition in the ‘on’ clause it will work. Thanks for the clarification, it is not a bug indeed. I will close the bug report. Sorry and thanks for the explanation.

EDIT: Maybe the docs need to be more detailed.

Also see this recent topic on the same issue:

http://www.yiiframework.com/forum/index.php?/topic/11283-scope-condition-in-join-clause/

Mike, the new version of AR does it! before i limit via WHERE, new version limits via LEFT JOIN. Look at queries that i posted.

LEFT JOIN does not limit. You should use INNER JOIN if you want that.