when $mylimit is set to 5 and $myoffset to 0, i've only 4 $mymodel objects back this cuz the limit / offset are relation consistent (and should be) but not the where causing the first row / object to be omited based on the where clause.
my table contains 10 records that matches the where clause, and one that doesn't, when querying with limit 5 and offset 0, i get only 4 records back (should be 5), when i get rid of the where clause I've 5 records back containing the record that doesn't match the where clause.
the where clause is for for a field in the primary model
in my previous comment when i've said that my table contains 10 records matching the where clause i'm talking about the table corresponding to the primary model
When I echo the generated query with DB::last_query() (I've tried to simplify as possible the query), I've something similar to this:
SELECT
`t0`.`c_0` AS `t0_c0`, `t0`.`c_1` AS `t0_c1`, `t0`.`c_2` AS `t0_c2`, `t0`.`c_3` AS `t0_c3`, `t0`.`id` AS `t0_c4`,
`t1`.`c_0` AS `t1_c0`, `t1`.`c_1` AS `t1_c1`, `t1`.`t0_id` AS `t1_c2`, `t1`.`t2_id` AS `t1_c3`, `t1`.`id` AS `t1_c4`,
`t2`.`c_0` AS `t2_c0`, `t2`.`c_1` AS `t2_c1`, `t2`.`id` AS `t2_c2`
FROM (
SELECT `t0`.`c_0`, `t0`.`c_1`, `t0`.`c_2`, `t0`.`c_3`, `t0`.`id` FROM `users` AS `t0` LIMIT 5 OFFSET 0
) AS `t0`
LEFT JOIN `relation_1_table` AS `t1` ON (`t0`.`id` = `t1`.`t0_id`)
LEFT JOIN `raltion_1-1_table` AS `t2` ON (`t1`.`t2_id` = `t2`.`id`)
WHERE (`t0`.`c_3` IN (2, 3))
So the problem is that the limit / offset are applied before the where clause on a nested select query, returning exactly 5 records, after what the where clause is 'eliminating' a record that doesn't match and I finish having only 4 records back, if the where clause can be applied to the nested select query my problem will be solve, but can't figure out how to do it using orm::find()
The limit and offset are where they should be, this is by design, to avoid getting incomplete results.
The correct question would be "why is the WHERE clause not in the subquery too". This suggests you running an old(er) version of FuelPHP, I've tested on 1.8/develop. In the current codebase this problem is solved.
Limit and Offset are applied on the parent model, to make sure the results are consistent. When you create a query with related objects, it could be that you get more or less records than defined by your limit.
If you want to limit on rows, then use the rows limit and rows offset methods. But understand that your results might be incomplete if you include related models, so do not use the results for anything else than tables, lists, or reports!
Donate
