Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
where, when using related models and limit / offset with relation consistence
  • Trying to retreive data using orm this way:

    $models = Model_Mymodel::find(
    'all',
    array(
    'related' => array(
    'realtion#1',                   // one to one relation
    'relation#1.realtion#1-1', // one to one nested relation
    ),
    'where' => array(array('mycolumn', 'IN', array('1', '2'))),
    'order_by' => array('myothercolumn' => $mysortdirection),
    'limit' => $mylimit,
    'offset' => $mystart
    )
    );

    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.

    any help is highly appreciated.
  • Not sure what the question is?

    You should get (at maximum) the first $mylimit records back from Mymodel that match the WHERE clause, with their related records.
  • 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.
  • I can't comment without knowing the query. Is your where clause on a field in your primary model, or on a field in a related model?
  • 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
  • I can not reproduce it, I get 5 records back, as expected.

    See http://bin.fuelphp.com/snippet/view/Ix for my test models, code, data and result.
  • 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()

    Hope you can see where my problem is !
    Many thx
  • 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.
  • Yeah, i'm using fuelphp 1.1, is it possible to solve this problem on that version ?
    Again thx for your time.
  • HarroHarro
    Accepted Answer
    No, That version is about 3 years old, and is no longer maintained.

    You could try to swap your current Orm package for the 1.8/develop version (https://github.com/fuel/orm/archive/1.8/develop.zip), perhaps that will work if there weren't too many other changes.

    But no guarantees...
  • hi,
    i have same problem in fuel 1.8 develop version (also update orm package by your link)
    and not fix!

    i use without limit for pagination and add limit and offset for main query.
    please help
  • Read the documentation.

    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!

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion