mirror of
https://github.com/doctrine/orm.git
synced 2026-03-23 22:42:18 +01:00
DDC-1800: Paginator results is wrong if your query use order by clause #2266
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @doctrinebot on GitHub (Apr 27, 2012).
Originally assigned to: @beberlei on GitHub.
Jira issue originally created by user mdrolet:
NOTE: I didn't try this on other database, I'm using Oracle.
if my original fetchJoin query use an order by clause, the results is not keeping the provided order by clause and re-order them by id.
here is my generated query to get the distinct records that get generated:
SELECT distinct ID0
FROM
(
SELECT f0_.id AS ID0, f0_.deal_type_id AS DEAL_TYPE_ID1, f0_.title AS TITLE2, f0_.deal_date AS DEAL_DATE3, f0_.amount AS AMOUNT4,
f0_.abstract AS ABSTRACT5, f0_.created_date AS CREATED_DATE6, f0_.last_updated_date AS LAST_UPDATED_DATE7,
f0_.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUS_ID10,
f1_.id AS ID11, f1_.role_id AS ROLE_ID12, f1_.role_type_id AS ROLE_TYPE_ID13, f2_.id AS ID14, f3_.id AS ID15, f4_.id AS ID16, f5_.id AS ID17,
c6_.id AS ID18, d7_.id AS ID19
FROM fo_deal f0_
INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
INNER JOIN fo_property_deal f3_ ON f0_.id = f3_.deal_id
INNER JOIN fo_property f4_ ON f3_.property_id = f4_.id
LEFT JOIN fo_property_asset f5_ ON f4_.id = f5_.property_id
LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
WHERE f1_.people_id = 2
AND f0_.object_status_id <> 3
AND f0_.publishing_status_id = 2
ORDER BY f0_.deal_date DESC, f0_.published_date DESC
)
running this query I get the id 30, 44 when the inner query return 44, 30
here is the query that should get generated to take care of the order by clause:
SELECT distinct ID0, rownum+
FROM
(
SELECT f0_.id AS ID0, f0_.deal_type_id AS DEAL_TYPE_ID1, f0_.title AS TITLE2, f0_.deal_date AS DEAL_DATE3, f0_.amount AS AMOUNT4,
f0_.abstract AS ABSTRACT5, f0_.created_date AS CREATED_DATE6, f0_.last_updated_date AS LAST_UPDATED_DATE7,
f0_.object_status_id AS OBJECT_STATUS_ID8, f0_.published_date AS PUBLISHED_DATE9, f0_.publishing_status_id AS PUBLISHING_STATUS_ID10,
f1_.id AS ID11, f1_.role_id AS ROLE_ID12, f1_.role_type_id AS ROLE_TYPE_ID13, f2_.id AS ID14, f3_.id AS ID15, f4_.id AS ID16, f5_.id AS ID17,
c6_.id AS ID18, d7_.id AS ID19
FROM fo_deal f0_
INNER JOIN fo_deal_role f1_ ON f0_.id = f1_.deal_id
INNER JOIN fo_people f2_ ON f1_.people_id = f2_.id
INNER JOIN fo_property_deal f3_ ON f0_.id = f3_.deal_id
INNER JOIN fo_property f4_ ON f3_.property_id = f4_.id
LEFT JOIN fo_property_asset f5_ ON f4_.id = f5_.property_id
LEFT JOIN co_asset c6_ ON f5_.asset_id = c6_.id
LEFT JOIN ds_record d7_ ON c6_.ds_id = d7_.id
WHERE f1_.people_id = 2
AND f0_.object_status_id <> 3
AND f0_.publishing_status_id = 2
ORDER BY f0_.deal_date DESC, f0_.published_date DESC
) ORDER BY rownum ASC
To fix this on the Paginator code:
file: ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
method: walkSelectStatement
change:
$sql = sprintf('SELECT b._, rownum as rn FROM (SELECT DISTINCT %s FROM (%s)) b', // AS *dctrn_result',
implode(', ', $sqlIdentifier), $sql);
for:
$sql = sprintf('SELECT b._, rownum as rn FROM (SELECT DISTINCT %s, numrow FROM (%s) ORDER BY numrow ASC) b', // AS *dctrn_result',
implode(', ', $sqlIdentifier), $sql);
@doctrinebot commented on GitHub (May 14, 2012):
Comment created by mdrolet:
rownum instead of numrow. sorry.
$sql = sprintf('SELECT b.*, rownum as rn FROM (SELECT DISTINCT %s, rownum FROM (%s) ORDER BY rownum ASC) b',
implode(', ', $sqlIdentifier), $sql);
@doctrinebot commented on GitHub (Jul 7, 2012):
Comment created by @beberlei:
Doctrine 2.2.2 doesnt have the LimitSubqueryoutputWalker and Doctrine 2.3-dev does not have the line in the code. Can you make a more explicit statement of where the change is necessary?
@doctrinebot commented on GitHub (Jul 9, 2012):
Comment created by mdrolet:
It's in the Pagination of version 2.2.2. ORM/Tools/Pagination/LimitSubqueryOutputWalker.php
@doctrinebot commented on GitHub (Jul 9, 2012):
Comment created by @beberlei:
This is the 2.2 branch, https://github.com/doctrine/doctrine2/tree/2.2/lib/Doctrine/ORM/Tools/Pagination and https://github.com/doctrine/doctrine2/tree/2.2.2/lib/Doctrine/ORM/Tools/Pagination is the 2.2.2 tag.
no LimitSubqueryOutputWalker.php in there.
@doctrinebot commented on GitHub (Aug 29, 2012):
Comment created by @beberlei:
Fixed
@doctrinebot commented on GitHub (Aug 29, 2012):
Issue was closed with resolution "Fixed"
@doctrinebot commented on GitHub (Apr 9, 2013):
Comment created by rkolbe:
This issue is popping it's head up again!
Benjamin, your tests don't test for the ordering problem unless those tests are happening somewhere else?
f55b5411c8I am performing a query as complex as Marc's and I experience the same exact issue. I have checked out today's latest master branch as well as the 2.3 tag with no change.
Please advise.
@doctrinebot commented on GitHub (Apr 9, 2013):
Comment created by rkolbe:
I have a PR in https://github.com/doctrine/doctrine2/pull/645