Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
ORM issue order_by, limit, offset, relations
  • Flats::query()->where('category_id', 1)
    ->related('prices')
    ->related('photos')
    ->related('stations')
    ->related('stations.from_station_list')
    ->relted('district')
    ->related('city')
    ->order_by('created_at', 'desc');
    ->limit($pagination->per_page)
    ->offset($pagination->offset)
    ->get();


    The query is:


    SELECT 
    `t0`.`id` AS `t0_c0`,
    ...
    ...
    ...
    ...
    ...
    `t6`.`shortname_pos` AS `t6_c11`
    FROM
    (
    SELECT
    `t0`.`id`,
    ...
    ...
    ...
    ...
    `t0`.`created_at`
    FROM `flats` AS `t0`
    WHERE `t0`.`category_id` = 1
    LIMIT 10
    OFFSET 0
    ) AS `t0`
    LEFT JOIN `prices` AS `t1` ON (`t0`.`id` = `t1`.`ad_id`)
    LEFT JOIN `photos` AS `t2` ON (`t0`.`id` = `t2`.`ad_id`)
    LEFT JOIN `stations` AS `t3` ON (`t0`.`id` = `t3`.`ad_id`)
    LEFT JOIN `stations_list` AS `t4` ON (`t3`.`station_id` = `t4`.`id`)
    LEFT JOIN `districts_list` AS `t5` ON (`t0`.`district_id` = `t5`.`id`)
    LEFT JOIN `fias` AS `t6` ON (`t0`.`city_id` = `t6`.`id`)
    ORDER BY `t0`.`created_at` DESC;


    How can I place ORDER BY `t0`.`created_at` DESC in the child select instead of the end of the query (parent select)? 

    Thanks
  • Upgrade to 1.6/develop, or wait until 1.6 is released (which will happen in the next few weeks).
  • Another one thing, if I add and_where_open/and_where_close with where and or_where conditions, the where query part appears in the parent select, but it should be in the child select. 

    But if and_where_open/and_where_close are omitted the where query appears correctly, in the child select.

    Can it be fixed?
    $query = Sale::query()
    ->where('category_id', 1)
    ->where('status', 1);
    ->and_where_open()
    ->where('rooms_count', 1)
    ->or_where('rooms_count', 2)
    ->or_where('rooms_count', 3)
    ->and_where_close()
    ->related('stations')
    ->related('photos')
    ->get();
  • ORM generates a standard join unless you have included limit() and offset(). Only then it uses a subquery.

    I don't really know why nested where's are skipped, but I think it has to do with the fact that all where clauses inside the group have to be on the main model. If you include one where on a column in one of the related table, it goes horribly wrong.

    I've looked at how this can be fixed, but it's not that easy. Can you create a ticket for this at https://github.com/fuel/orm/issues with this example?

Howdy, Stranger!

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

In this Discussion