Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
ORM order_by being ignored/improperly placed into query
  • vesselinvvesselinv
    I'm having an issue with the order_by() method. The following call returns a resultset in the wrong order and it appears that limit() is acting like you're calling rows_limit



    $statuses = Model_Status::query()
                ->related('user', array(
                      'related'      => array('followers'),
                   )
                )
                ->related('favourites')
                ->related('comments', array('related' => array('user')))
                ->related('reposts', array('related' => array('user')))
                ->related('status_origin')
                ->where('user.followers.follower_user_id', 16)
                ->where('id', '<', 2145)
                ->order_by('created_at', 'desc')
                ->limit(20)
                ->get();


    First of all the order returned is ASC instead of the requested DESC and secondly limit is acting the same way as rows_limit. Troubleshooting the query that is being generated, ORDER BY is not being inserted in the right spot.




    SELECT 
        `gm_t0`.`id` AS `t0_c0`,
        `gm_t0`.`user_id` AS `t0_c1`,
        `gm_t0`.`in_reply_to_status_id` AS `t0_c2`,
        `gm_t0`.`repost_of_status_id` AS `t0_c3`,
        `gm_t0`.`text` AS `t0_c4`,
        `gm_t0`.`attachment_pic` AS `t0_c5`,
        `gm_t0`.`attachment_vid` AS `t0_c6`,
        `gm_t0`.`geo_latitude` AS `t0_c7`,
        `gm_t0`.`geo_longitude` AS `t0_c8`,
        `gm_t0`.`geo_location` AS `t0_c9`,
        `gm_t0`.`created_at` AS `t0_c10`,
        `gm_t1`.`gender` AS `t1_c0`,
        `gm_t1`.`verified` AS `t1_c1`,
        `gm_t1`.`blocked` AS `t1_c2`,
        `gm_t1`.`protected_statuses` AS `t1_c3`,
        `gm_t1`.`profile_picture_url` AS `t1_c4`,
        `gm_t1`.`profile_background_url` AS `t1_c5`,
        `gm_t1`.`receive_notifications_messages` AS `t1_c6`,
        `gm_t1`.`receive_notifications_kisses` AS `t1_c7`,
        `gm_t1`.`receive_notifications_news` AS `t1_c8`,
        `gm_t1`.`id` AS `t1_c9`,
        `gm_t1`.`screen_name` AS `t1_c10`,
        `gm_t1`.`email` AS `t1_c11`,
        `gm_t1`.`password` AS `t1_c12`,
        `gm_t1`.`name` AS `t1_c13`,
        `gm_t1`.`description` AS `t1_c14`,
        `gm_t1`.`location` AS `t1_c15`,
        `gm_t1`.`date_of_birth` AS `t1_c16`,
        `gm_t1`.`last_login` AS `t1_c17`,
        `gm_t1`.`last_ip` AS `t1_c18`,
        `gm_t1`.`latitude` AS `t1_c19`,
        `gm_t1`.`longitude` AS `t1_c20`,
        `gm_t1`.`created_at` AS `t1_c21`,
        `gm_t1`.`language` AS `t1_c22`,
        `gm_t1`.`country` AS `t1_c23`,
        `gm_t2`.`id` AS `t2_c0`,
        `gm_t2`.`follower_user_id` AS `t2_c1`,
        `gm_t2`.`followee_user_id` AS `t2_c2`,
        `gm_t2`.`created_at` AS `t2_c3`,
        `gm_t3`.`id` AS `t3_c0`,
        `gm_t3`.`user_id` AS `t3_c1`,
        `gm_t3`.`status_id` AS `t3_c2`,
        `gm_t3`.`created_at` AS `t3_c3`,
        `gm_t4`.`id` AS `t4_c0`,
        `gm_t4`.`user_id` AS `t4_c1`,
        `gm_t4`.`in_reply_to_status_id` AS `t4_c2`,
        `gm_t4`.`repost_of_status_id` AS `t4_c3`,
        `gm_t4`.`text` AS `t4_c4`,
        `gm_t4`.`attachment_pic` AS `t4_c5`,
        `gm_t4`.`attachment_vid` AS `t4_c6`,
        `gm_t4`.`geo_latitude` AS `t4_c7`,
        `gm_t4`.`geo_longitude` AS `t4_c8`,
        `gm_t4`.`geo_location` AS `t4_c9`,
        `gm_t4`.`created_at` AS `t4_c10`,
        `gm_t5`.`gender` AS `t5_c0`,
        `gm_t5`.`verified` AS `t5_c1`,
        `gm_t5`.`blocked` AS `t5_c2`,
        `gm_t5`.`protected_statuses` AS `t5_c3`,
        `gm_t5`.`profile_picture_url` AS `t5_c4`,
        `gm_t5`.`profile_background_url` AS `t5_c5`,
        `gm_t5`.`receive_notifications_messages` AS `t5_c6`,
        `gm_t5`.`receive_notifications_kisses` AS `t5_c7`,
        `gm_t5`.`receive_notifications_news` AS `t5_c8`,
        `gm_t5`.`id` AS `t5_c9`,
        `gm_t5`.`screen_name` AS `t5_c10`,
        `gm_t5`.`email` AS `t5_c11`,
        `gm_t5`.`password` AS `t5_c12`,
        `gm_t5`.`name` AS `t5_c13`,
        `gm_t5`.`description` AS `t5_c14`,
        `gm_t5`.`location` AS `t5_c15`,
        `gm_t5`.`date_of_birth` AS `t5_c16`,
        `gm_t5`.`last_login` AS `t5_c17`,
        `gm_t5`.`last_ip` AS `t5_c18`,
        `gm_t5`.`latitude` AS `t5_c19`,
        `gm_t5`.`longitude` AS `t5_c20`,
        `gm_t5`.`created_at` AS `t5_c21`,
        `gm_t5`.`language` AS `t5_c22`,
        `gm_t5`.`country` AS `t5_c23`,
        `gm_t6`.`id` AS `t6_c0`,
        `gm_t6`.`user_id` AS `t6_c1`,
        `gm_t6`.`in_reply_to_status_id` AS `t6_c2`,
        `gm_t6`.`repost_of_status_id` AS `t6_c3`,
        `gm_t6`.`text` AS `t6_c4`,
        `gm_t6`.`attachment_pic` AS `t6_c5`,
        `gm_t6`.`attachment_vid` AS `t6_c6`,
        `gm_t6`.`geo_latitude` AS `t6_c7`,
        `gm_t6`.`geo_longitude` AS `t6_c8`,
        `gm_t6`.`geo_location` AS `t6_c9`,
        `gm_t6`.`created_at` AS `t6_c10`,
        `gm_t7`.`gender` AS `t7_c0`,
        `gm_t7`.`verified` AS `t7_c1`,
        `gm_t7`.`blocked` AS `t7_c2`,
        `gm_t7`.`protected_statuses` AS `t7_c3`,
        `gm_t7`.`profile_picture_url` AS `t7_c4`,
        `gm_t7`.`profile_background_url` AS `t7_c5`,
        `gm_t7`.`receive_notifications_messages` AS `t7_c6`,
        `gm_t7`.`receive_notifications_kisses` AS `t7_c7`,
        `gm_t7`.`receive_notifications_news` AS `t7_c8`,
        `gm_t7`.`id` AS `t7_c9`,
        `gm_t7`.`screen_name` AS `t7_c10`,
        `gm_t7`.`email` AS `t7_c11`,
        `gm_t7`.`password` AS `t7_c12`,
        `gm_t7`.`name` AS `t7_c13`,
        `gm_t7`.`description` AS `t7_c14`,
        `gm_t7`.`location` AS `t7_c15`,
        `gm_t7`.`date_of_birth` AS `t7_c16`,
        `gm_t7`.`last_login` AS `t7_c17`,
        `gm_t7`.`last_ip` AS `t7_c18`,
        `gm_t7`.`latitude` AS `t7_c19`,
        `gm_t7`.`longitude` AS `t7_c20`,
        `gm_t7`.`created_at` AS `t7_c21`,
        `gm_t7`.`language` AS `t7_c22`,
        `gm_t7`.`country` AS `t7_c23`,
        `gm_t8`.`id` AS `t8_c0`,
        `gm_t8`.`user_id` AS `t8_c1`,
        `gm_t8`.`in_reply_to_status_id` AS `t8_c2`,
        `gm_t8`.`repost_of_status_id` AS `t8_c3`,
        `gm_t8`.`text` AS `t8_c4`,
        `gm_t8`.`attachment_pic` AS `t8_c5`,
        `gm_t8`.`attachment_vid` AS `t8_c6`,
        `gm_t8`.`geo_latitude` AS `t8_c7`,
        `gm_t8`.`geo_longitude` AS `t8_c8`,
        `gm_t8`.`geo_location` AS `t8_c9`,
        `gm_t8`.`created_at` AS `t8_c10`
    FROM
        (SELECT 
            `gm_t0`.`id`,
                `gm_t0`.`user_id`,
                `gm_t0`.`in_reply_to_status_id`,
                `gm_t0`.`repost_of_status_id`,
                `gm_t0`.`text`,
                `gm_t0`.`attachment_pic`,
                `gm_t0`.`attachment_vid`,
                `gm_t0`.`geo_latitude`,
                `gm_t0`.`geo_longitude`,
                `gm_t0`.`geo_location`,
                `gm_t0`.`created_at`
        FROM
            `gm_statuses` AS `gm_t0`
        WHERE
            `gm_t0`.`id` < '2160'
                AND `gm_t0`.`in_reply_to_status_id` IS null
        LIMIT 20) AS `gm_t0`
            LEFT JOIN
        `gm_users` AS `gm_t1` ON (`gm_t0`.`user_id` = `gm_t1`.`id`)
            LEFT JOIN
        `gm_followers` AS `gm_t2` ON (`gm_t1`.`id` = `gm_t2`.`followee_user_id`)
            LEFT JOIN
        `gm_favourites` AS `gm_t3` ON (`gm_t0`.`id` = `gm_t3`.`status_id`)
            LEFT JOIN
        `gm_statuses` AS `gm_t4` ON (`gm_t0`.`id` = `gm_t4`.`in_reply_to_status_id`)
            LEFT JOIN
        `gm_users` AS `gm_t5` ON (`gm_t4`.`user_id` = `gm_t5`.`id`)
            LEFT JOIN
        `gm_statuses` AS `gm_t6` ON (`gm_t0`.`id` = `gm_t6`.`repost_of_status_id`)
            LEFT JOIN
        `gm_users` AS `gm_t7` ON (`gm_t6`.`user_id` = `gm_t7`.`id`)
            LEFT JOIN
        `gm_statuses` AS `gm_t8` ON (`gm_t0`.`repost_of_status_id` = `gm_t8`.`id`)
    WHERE
        `gm_t2`.`follower_user_id` = 16
    ORDER BY `gm_t0`.`created_at` DESC


    What should be happening (or at least what I need it to do) is place the ORDER_BY (very last line) into the main table - subquery right after FROM. Running the query with that modification I get what I need with the proper limit count. I tried setting the order_by into the model's conditions but am still getting the same result. Would really appreciate it if anyone could give me some hints. 

  • HarroHarro
    If you want to order on a related column, the order_by clause should be in the related array, not at the main table level. See http://docs.fuelphp.com/packages/orm/relations/intro.html#usage_rel_conditions for an example.

    And the limit is on the subquery, which is what happens if you use limit(). If you want to limit the entire joined result (for pagination reasons for example), you have to use rows_limit().
  • vesselinvvesselinv
    Thanks for your reply, Harro. Both order_by() and limit() should go on the main table (Model_Status - gm_t0 in the query) level in my case, not the relations. However, because order_by() is inserted at the end of the entire query and limit() into the main table select statement, I'm getting the results into asc order. Changing it to ->order_by('gm_t0', 'desc') returns the same results as it gets appended at the end and it's already fetched the first 20 records from gm_t0.
  • HarroHarro
    Huh?

    Order is processed by the RDBMS after the results have been obtained. The limit has already been processed, only the resulting rows have to be sorted. I can't see why that query would return rows ordered by "gm_t0.created_by ASC". The location of the statement in the SQL is not relevant.



  • vesselinvvesselinv
    Results with generated query

    What it should be 

    Not sure if it's bug or ORM was just designed that way.
  • HarroHarro
    Accepted Answer
    I see the difference, but I'm trying to understand what it would mean to the result.

    In the end, the gm_t0.created_at value un the endresult will be exactly the same values, as they come from the same subquery. Due to the MySQL query optimizer, you have no idea how the query is actually processed, so in actual fact I don't think there should be a difference in output between the two.

    You can try running an EXPLAIN EXTENDED on both queries, and see it that sheds any light.
  • vesselinvvesselinv
    I'll try that.

    I may just end up doing it the old-fashioned way - writing my own queries. Thanks for your time though!
  • HarroHarro
    I hope you figure it out and let us know, as the location of the order_by should have absolutely no influence on the result, given the way the optimizer works.
  • BaldinofBaldinof
    Hello,
    I reopen this topic, because I have exactly the same question.

    The problem: when having a query with related + limit/offset + order_by, the result are different of what's expected.

    As Vesselinv said, the problem is with the order_by position. I really think it's should be in the subquery.

    Why ?

    When MySQL runs the subquery it limits the result with the given limit. I understand the reason of placing limit in subquery and i'm agree. 
    But without the order_by in subquery, MySQL is ordering by the ID (by entry order in table). So when limiting, it's not limiting on the good result and finally the joins are not on good result too.

    Example : 
    $foo = Model_Post::query()
              ->related('comments')
              ->order_by('publish_date')
              ->limit(4)
              ->get();

    $bar = Model_Post::query()
              ->order_by('publish_date')
              ->limit(4)
              ->get();

    Actually the result will not be the same ! (if the publish_date sort are different of the entry date in table of course)

    It's not a normal behavior to have different result when use eager or lazy relation.

    I hope i've been understandable.
  • HarroHarro
    Can you add a ticket for this at https://github.com/fuel/core/issues, with this description?
  • BaldinofBaldinof
    Done in orm section.
Add a Comment

Howdy, Stranger!

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

Sign In Apply for Membership

Categories

In this Discussion