Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Major Bug In Cactiverecord::findall With Join And Limit #1846

Closed
cebe opened this Issue Dec 12, 2012 · 3 comments

Comments

4 participants
Owner

cebe commented Dec 12, 2012

Just copying a problem described in the forum: http://www.yiiframework.com/forum/index.php/topic/38375-major-bug-in-cactiverecordfindall-with-join-and-limit/

This will be a way to reproduce it, I didn't have time to see how to solve it:

  1. create two tables in mysql:

    • table1: (id - pk autoincrement)
    • table2: (id - pk autoincrement, table1_id - fk)
  2. insert rows, the number of inserted rows is importent:

    • table1:
      INSERT INTOtable1SETid= NULL; execute it 4 times

    • table1:

      INSERT INTO `table2` SET `table_id` = 1; execute it 3 times  
      INSERT INTO `table2` SET `table_id` = 2; execute it 2 times  
      INSERT INTO `table2` SET `table_id` = 3; execute it 1 time
      
  3. generate models with gii:

  4. create criteria:

     $criteria = new CDbCriteria();
     $criteria->with = array(
         "table2s" => array(
             "joinType" => "INNER JOIN",
         ),
     );
    

    4.1) execute criteria with findAll and get the count of rows:
    Table1::model()->findAll($criteria); // returns 3 results which is exactly what it should be
    query from log:

SELECT 
    `t`.`id` AS `t0_c0`, 
    `table2s`.`id` AS `t1_c0`, 
    `table2s`.`table1_id` AS `t1_c1` 
FROM `table1` `t`  
INNER JOIN `table2` `table2s` ON (`table2s`.`table1_id`=`t`.`id`)

4.2) add limit to the criteria and check the new results:
$criteria->limit = 5;
Table1::model()->findAll($criteria); // returns 4 results, it executes two queries, it doesn't take into account joinType
queries from log:

SELECT `t`.`id` AS `t0_c0` FROM `table1` `t`  LIMIT 5;
SELECT 
    `t`.`id` AS `t0_c0`, 
    `table2s`.`id` AS `t1_c0`, 
    `table2s`.`table1_id` AS `t1_c1` 
FROM `table1` `t` 
INNER JOIN `table2` `table2s` ON (`table2s`.`table1_id`=`t`.`id`)  
WHERE (`t`.`id` IN ('1', '2', '3', '4'));

4.3) add limit and together on true to the criteria and check the new results:
$criteria->limit = 5;
$criteria->together = true;
Table1::model()->findAll($criteria); // returns 2 results
query from log:

SELECT 
    `t`.`id` AS `t0_c0`, 
    `table2s`.`id` AS `t1_c0`, 
    `table2s`.`table1_id` AS `t1_c1` 
FROM `table1` `t`  
INNER JOIN `table2` `table2s` ON (`table2s`.`table1_id`=`t`.`id`)  
LIMIT 5

@cebe cebe was assigned Dec 12, 2012

This one cost me an hour today.. I'll see if I can fix.

Member

klimov-paul commented Oct 2, 2013

I can’t see the way this could be fixed.
‘table1’ and ‘table2’ have ‘HAS_MANY’ relation type: ‘table1’ has many ‘table2’. This means there is no way to select both ‘table1’ and ‘table2’ by single SQL with limit on ‘table1’.
If you select ‘table1 INNER JOIN table2’, the Cartesian product will result 1 + 2 + 3 = 6 records. Adding SQL limit 5 reduces this result to 5 and selection with particular ‘table1.id’ is lost. Then Yii loops the result grouping it by ‘table1.id’ using PHP methods. This causes wrong end result.
When you setup limit for the relational query, you setup limit on Cartesian product result, not on some records of the particular table.
The only way to fix this behavior is applying limit using PHP but not SQL in the case of relational query. But in such way why do need this limit then? – it will not help to reduce resource consumption. If you have 10000 records in the ‘table1’ your program will end with memory limit overflow error. Even if this is acceptable for you, you can use ‘CArrayDataProvider’ with page size set to your limit value (in provided example - 5) and simple ‘findAll()’ without limit.

Owner

samdark commented Oct 2, 2013

I agree. It just can't be done in a single query.

@samdark samdark closed this Oct 2, 2013

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment