Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Tools
    • Security Level: All
    • Labels:

      Description

      We are using the doctrine pagination tool for faster queries in paginated results.

      But doctrine is now creating the following query:

      SELECT DISTINCT id0 FROM (
      SELECT c0_.id AS id0, c0_.number AS number1
      FROM cudi.stock_orders_items c0_
      INNER JOIN cudi.stock_orders c1_ ON c0_.order_id = c1_.id
      WHERE c1_.date_ordered IS NOT NULL AND c1_.date_ordered > $1 AND c1_.date_ordered < $2
      ORDER BY c1_.date_ordered DESC
      ) dctrn_result LIMIT 25 OFFSET 50

      But due to the distinct, the order is lost and the results are not sorted by date_ordered anymore. How can this be solved?

      Thanks

        Activity

        Hide
        Marco Pivetta added a comment -

        Kristof Coomans is the order lost already in this result or in the `WHERE IN()` subsequent query?

        Show
        Marco Pivetta added a comment - Kristof Coomans is the order lost already in this result or in the `WHERE IN()` subsequent query?
        Hide
        Kristof Mariën added a comment -

        I was digging in the paginator code and this is the query used to select the ids in \Doctrine\ORM\Tools\Pagination\Paginator (line 173)

        The subquery from above, outputs the results in the right order. But after applying the distinct the order is messed up. I think this problem needs to be solved by the preserveSqlOrdering function in LimitSubqueryOutputWalker. But this function doesn't add a column to sort on...

        Show
        Kristof Mariën added a comment - I was digging in the paginator code and this is the query used to select the ids in \Doctrine\ORM\Tools\Pagination\Paginator (line 173) The subquery from above, outputs the results in the right order. But after applying the distinct the order is messed up. I think this problem needs to be solved by the preserveSqlOrdering function in LimitSubqueryOutputWalker. But this function doesn't add a column to sort on...
        Hide
        Benjamin Eberlei added a comment -

        Kristof Mariën Since you have the example already, so you mean the ORDER BY has to be part of the subquery and ALSO added to the outer query? That actually makes sense.

        Show
        Benjamin Eberlei added a comment - Kristof Mariën Since you have the example already, so you mean the ORDER BY has to be part of the subquery and ALSO added to the outer query? That actually makes sense.
        Hide
        Kristof Mariën added a comment -

        That would solve the problem for me.

        Show
        Kristof Mariën added a comment - That would solve the problem for me.
        Hide
        Kristof Mariën added a comment -

        Any progress on this issue?

        Show
        Kristof Mariën added a comment - Any progress on this issue?
        Hide
        Kristof Mariën added a comment -

        I have found another query with the same problem. The common thing they have is that I want to order on a column of a joined entity.

        Show
        Kristof Mariën added a comment - I have found another query with the same problem. The common thing they have is that I want to order on a column of a joined entity.
        Hide
        Kristof Mariën added a comment -

        After digging in the code I found that I just had to add the joined entity to my select statement. This issue can be closed

        Show
        Kristof Mariën added a comment - After digging in the code I found that I just had to add the joined entity to my select statement. This issue can be closed

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Kristof Mariën
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: