yii: Relational INNER JOIN queries using LIMIT give wrong results

Hi,

Consider having two tables, ‘parent’ and ‘children’, where children table has a foreign key to parent. The tables have the following contents:

PARENT     CHILDREN
id           id     parent_id
1            1      1
2            2      1
3            3      1
             4      2
             5      2
             6      2
             7      3
             8      3
             9      3

And also consider the following code to find all parents with their children that have child with id 6 or less.

$result = Parent::model()->with(array(
    'children' => array(
        'joinType' => 'INNER JOIN', 
        'condition' => 'children.id <= 6'
    ), 
))->findAll();

This code works fine. It returns parents 1 and 2 with relations to the first 6 children.

Now however, consider the code below. The difference being that I added a limit of 3 parents:

$result = Parent::model()->with(array(
    'children' => array(
        'joinType' => 'INNER JOIN', 
        'condition' => 'children.id <= 6'
    ), 
))->findAll(array('limit' => 3));

Now all of a sudden 3 parents are returned, with the last parent (id 3) having no related children. This is not expected behavior in my opinion. I should get the same results as with the first statement.

In my view, the cause for this behavior is that by adding the limit clause, Yii will use two queries to fetch the result. The first query fetches all parents, limited by 3. And the second query joins all children to those 3 parents, see the querylog below:

SELECT `t`.`id` AS `t0_c0` FROM `parent` `t` LIMIT 3;

SELECT `t`.`id` AS `t0_c0`, `children`.`id` AS `t1_c0`, 
`children`.`parent_id` AS `t1_c1` FROM `parent` `t` 
INNER JOIN `children` ON (`children`.`parent_id`=`t`.`id`) 
WHERE (`t`.`id` IN (1, 2, 3)) AND (children.id <= 6);

This behavior is correct for LEFT OUTER JOIN’s (the Yii default). But when using INNER JOIN the join condition can actually affect the results of the parent query. The parent with id 3 should not be returned.

In the second query, Yii does select t.id, which refers to the parent id. it could look at whether all previously selected parents are also returned by the second query. If not, it should omit them in the returned results.

Using the ‘together’ option is no solution in this instance, as it would return the first 3 results of the composite query, which would result in getting 1 parent with 3 children.

About this issue

Original URL
State: closed
Created 11 years ago
Comments: 28 (15 by maintainers)

Most upvoted comments

Could you explain why this issue won’t be addressed?

Once again: when you are using eager loading for the “HAS_MANY” relation with limit, Yii always(!) performs 2(!) queries: first to fetch parent records applying specified limit, second – to fetch related children records. Thus it does NOT matter if you specify join as “INNER JOIN” or “LEFT JOIN” – 2 separated queries are performed and then results are combined using PHP.

Do you consider it impossible?

Yes, it is impossible to fetch parent records alone with all(!) their children, while applying limit(!) on parent records at the single(!) SQL query. If you specify limit on query:

SELECT * 
FROM `parent` `t` 
INNER JOIN `children` ON (`children`.`parent_id`=`t`.`id`)
LIMIT 3

this limit applies on the end join result, but not for ‘parent’ rows only. If you have parent with parent_id = 1, which has 3 children, in the result of this query only this parent_id = 1 will appear.

I want to filter the parents by means of joining the children.

Well this is easy:

$criteria = array(
   ‘with’ => array(
       'children' => array(
           'together' => true,
           'joinType' => 'INNER JOIN', 
           'condition' => 'children.id <= 6'
       ), 
   ),
   'limit' => 3,
   'group' => ‘t.id’,
);
$rows = Parent::model()->findAll($criteria);

klimov-paul on Dec 6, 2013

There must be another query before this that brings back the id list according to limit and offset.

You are eager loading a HAS_MANY relation and are limiting the main records. In order to do this correctly, Yii breaks it into two queries: the first query does the limiting and brings back the main record IDs, and the second query does what you show here.

To work around the issue, you may start to build your query from opposite side and use “group by”:

$criteria = array(
   ‘with’ => array(
       'parent' => array(
           'joinType' => 'INNER JOIN', 
           'condition' => 'children.id <= 6'
       ), 
   ),
   'limit' => 3,
   'group' => ‘parent.id’,
);
$rows = Child::model()->findAll($criteria);

klimov-paul on Dec 6, 2013