Jira issue originally created by user krmarien:
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.stockorders_items c0
INNER JOIN cudi.stockorders c1_ ON c0_.order_id = c1.id
WHERE c1.date_ordered IS NOT NULL AND c1_.date_ordered > $1 AND c1_.dateordered < $2
ORDER BY c1.dateordered 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?
Comment created by @ocramius:
[~cyberwolf] is the order lost already in this result or in the WHERE IN() subsequent query?
Comment created by krmarien:
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...
Comment created by @beberlei:
[~krmarien] 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.
That would solve the problem for me.
Any progress on this issue?
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.
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
Issue was closed with resolution "Won't Fix"