doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

DDC-3679: Paginator generates broken SQL when paginating ordered query #4520

New Issue
Closed
opened 2026-01-22 14:43:58 +01:00 by admin · 7 comments
admin commented 2026-01-22 14:43:58 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Apr 8, 2015).

Originally assigned to: @ostrolucky on GitHub.

Jira issue originally created by user hosiplan:

I have a following DQL

SELECT ra FROM Damejidlo\Recipes\RecipeAuthor ra ORDER BY ra.id ASC

Which generates following SQL

SELECT r0*.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_ ORDER BY r0*.id ASC

Now I wanna paginate it

$query = $em->createQuery($dql);
$paginated = new \Doctrine\ORM\Tools\Pagination\Paginator($query, TRUE);

the generated SQL Count query is

SELECT COUNT(*) AS dctrn*count FROM (SELECT DISTINCT id_0 FROM (SELECT r0_.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_ ORDER BY r0_.id ASC) dctrn_result) dctrn*table

Next executed query is the query that selects ids for where in

SELECT DISTINCT id*0 FROM (SELECT r0_.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_) dctrn_result ORDER BY r0*.id ASC LIMIT 5 OFFSET 0

And there goes my problem

Doctrine\DBAL\Exception\InvalidFieldNameException

An exception occurred while executing '...': SQLSTATE[42S22]: Column not found: 1054 Unknown column 'r0_.id' in 'order clause'

I'm going to investigate more, I just wanned to open an issue for now so you're aware.

Originally created by @doctrinebot on GitHub (Apr 8, 2015). Originally assigned to: @ostrolucky on GitHub. Jira issue originally created by user hosiplan: I have a following DQL ``` sql SELECT ra FROM Damejidlo\Recipes\RecipeAuthor ra ORDER BY ra.id ASC ``` Which generates following SQL ``` sql SELECT r0*.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_ ORDER BY r0*.id ASC ``` Now I wanna paginate it ``` javascript $query = $em->createQuery($dql); $paginated = new \Doctrine\ORM\Tools\Pagination\Paginator($query, TRUE); ``` the generated SQL Count query is ``` sql SELECT COUNT(*) AS dctrn*count FROM (SELECT DISTINCT id_0 FROM (SELECT r0_.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_ ORDER BY r0_.id ASC) dctrn_result) dctrn*table ``` Next executed query is the query that selects ids for where in ``` sql SELECT DISTINCT id*0 FROM (SELECT r0_.id AS id_0, r0_.name AS name_1, r0_.perex AS perex_2, r0_.is_active AS is_active_3, r0_.on_homepage AS on_homepage_4, r0_.content AS content_5, r0_.photo AS photo_6, r0_.link AS link_7 FROM recipes_authors r0_) dctrn_result ORDER BY r0*.id ASC LIMIT 5 OFFSET 0 ``` And there goes my problem ``` Doctrine\DBAL\Exception\InvalidFieldNameException An exception occurred while executing '...': SQLSTATE[42S22]: Column not found: 1054 Unknown column 'r0_.id' in 'order clause' ``` I'm going to investigate more, I just wanned to open an issue for now so you're aware.
admin added the Bug label 2026-01-22 14:43:58 +01:00
admin closed this issue 2026-01-22 14:43:58 +01:00
admin commented 2026-01-22 14:43:59 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 8, 2015):

Comment created by hosiplan:

aa80c7d!orm $> bisect start
aa80c7d!orm $> bisect bad
aa80c7d!orm $> bisect good v2.4.7
Bisecting: a merge base must be tested
[29d6da0fa063d55d06117045f3446b2716202d2b] Merge pull request #703 from shulcsm/patch-1
29d6da0!orm $> bisect good
Bisecting: 912 revisions left to test after this (roughly 10 steps)
[a2e0133a94bf235498ee2e805c8ca9a147b7fa24] Adding [DDC-3276](http://www.doctrine-project.org/jira/browse/DDC-3276) test group
a2e0133!orm $> bisect good
Bisecting: 456 revisions left to test after this (roughly 9 steps)
[dde09872df4f442f3a2a4c9ce6053badb8b49012] #1172 - writing a more concise test case about merging detached proxies
dde0987!orm $> bisect good
Bisecting: 233 revisions left to test after this (roughly 8 steps)
[d024193cc0d106d4939bbf698f840ef122602d64] Merge pull request #1272 from Ocramius/hotfix/[DDC-2704](http://www.doctrine-project.org/jira/browse/DDC-2704)-merge-inherited-transient-properties
d024193!orm $> bisect good
Bisecting: 116 revisions left to test after this (roughly 7 steps)
[6cf76158a07219529631f7b8fb43c3b92d3552be] Merge pull request #1329 from Wilt/patch-1
6cf7615!orm $> bisect good
Bisecting: 58 revisions left to test after this (roughly 6 steps)
[d1a695b42b54ed7deb2f9461fee7d02d32514c3a] Typo in phpdoc
d1a695b!orm $> bisect good
Bisecting: 28 revisions left to test after this (roughly 5 steps)
[b923c937e2194ae38c88e31cfcea8d06b58603fc] Merge branch 'hotfix/#1352-entity-generator-new-class-metadata-hotfix'
b923c93!orm $> bisect good
Bisecting: 14 revisions left to test after this (roughly 4 steps)
[6c5dbd8d4ca4fd68f0d5b2de467c3d2a59e0173c] #1353 #1347 #1351 - Removing double quotes (confusing)
6c5dbd8!orm $> bisect bad
Bisecting: 6 revisions left to test after this (roughly 3 steps)
[af3f5c5c5a93daf240c2daae4eddf9a0628eeb19] Add test for paginating on a query with a subquery in the where clause
af3f5c5!orm $> bisect good
Bisecting: 3 revisions left to test after this (roughly 2 steps)
[b76107e20f7fa893c2e4945a373ff624ed00c0d2] resolve review comments from @stof
b76107e!orm $> bisect bad
Bisecting: 0 revisions left to test after this (roughly 1 step)
[edcc0fc024e0d33a4f6159c006c0f393386dac3a] Fix paginator when ordering by while selecting entities using joined table inheritance
edcc0fc!orm $> bisect bad
Bisecting: 0 revisions left to test after this (roughly 0 steps)
[09d28819b520b9219bd6f41e8c6e0f4449a3807b] Fix issue where paginating on a query with a subquery in the where clause crashed
09d2881!orm $> bisect good
edcc0fc024e0d33a4f6159c006c0f393386dac3a is the first bad commit
commit edcc0fc024e0d33a4f6159c006c0f393386dac3a
Author: Bill Schaller <bill@zeroedin.com>
Date:   Mon Mar 30 12:16:50 2015 -0400

    Fix paginator when ordering by while selecting entities using joined table inheritance

:040000 040000 aa2bd1b6cb6dc10ef39c3ea1affe78c75bb2b778 4dc9a0e15f847bfc940096263a639d6f1a4eb618 M      lib
:040000 040000 fa9a72d3eaae021b0015e43bbe64a9cf7eadf46e 7b6821dd22bee36c808e4680a427aa3ec75faad0 M      tests
09d2881!orm $>
@doctrinebot commented on GitHub (Apr 8, 2015): Comment created by hosiplan: ``` aa80c7d!orm $> bisect start aa80c7d!orm $> bisect bad aa80c7d!orm $> bisect good v2.4.7 Bisecting: a merge base must be tested [29d6da0fa063d55d06117045f3446b2716202d2b] Merge pull request #703 from shulcsm/patch-1 29d6da0!orm $> bisect good Bisecting: 912 revisions left to test after this (roughly 10 steps) [a2e0133a94bf235498ee2e805c8ca9a147b7fa24] Adding [DDC-3276](http://www.doctrine-project.org/jira/browse/DDC-3276) test group a2e0133!orm $> bisect good Bisecting: 456 revisions left to test after this (roughly 9 steps) [dde09872df4f442f3a2a4c9ce6053badb8b49012] #1172 - writing a more concise test case about merging detached proxies dde0987!orm $> bisect good Bisecting: 233 revisions left to test after this (roughly 8 steps) [d024193cc0d106d4939bbf698f840ef122602d64] Merge pull request #1272 from Ocramius/hotfix/[DDC-2704](http://www.doctrine-project.org/jira/browse/DDC-2704)-merge-inherited-transient-properties d024193!orm $> bisect good Bisecting: 116 revisions left to test after this (roughly 7 steps) [6cf76158a07219529631f7b8fb43c3b92d3552be] Merge pull request #1329 from Wilt/patch-1 6cf7615!orm $> bisect good Bisecting: 58 revisions left to test after this (roughly 6 steps) [d1a695b42b54ed7deb2f9461fee7d02d32514c3a] Typo in phpdoc d1a695b!orm $> bisect good Bisecting: 28 revisions left to test after this (roughly 5 steps) [b923c937e2194ae38c88e31cfcea8d06b58603fc] Merge branch 'hotfix/#1352-entity-generator-new-class-metadata-hotfix' b923c93!orm $> bisect good Bisecting: 14 revisions left to test after this (roughly 4 steps) [6c5dbd8d4ca4fd68f0d5b2de467c3d2a59e0173c] #1353 #1347 #1351 - Removing double quotes (confusing) 6c5dbd8!orm $> bisect bad Bisecting: 6 revisions left to test after this (roughly 3 steps) [af3f5c5c5a93daf240c2daae4eddf9a0628eeb19] Add test for paginating on a query with a subquery in the where clause af3f5c5!orm $> bisect good Bisecting: 3 revisions left to test after this (roughly 2 steps) [b76107e20f7fa893c2e4945a373ff624ed00c0d2] resolve review comments from @stof b76107e!orm $> bisect bad Bisecting: 0 revisions left to test after this (roughly 1 step) [edcc0fc024e0d33a4f6159c006c0f393386dac3a] Fix paginator when ordering by while selecting entities using joined table inheritance edcc0fc!orm $> bisect bad Bisecting: 0 revisions left to test after this (roughly 0 steps) [09d28819b520b9219bd6f41e8c6e0f4449a3807b] Fix issue where paginating on a query with a subquery in the where clause crashed 09d2881!orm $> bisect good edcc0fc024e0d33a4f6159c006c0f393386dac3a is the first bad commit commit edcc0fc024e0d33a4f6159c006c0f393386dac3a Author: Bill Schaller <bill@zeroedin.com> Date: Mon Mar 30 12:16:50 2015 -0400 Fix paginator when ordering by while selecting entities using joined table inheritance :040000 040000 aa2bd1b6cb6dc10ef39c3ea1affe78c75bb2b778 4dc9a0e15f847bfc940096263a639d6f1a4eb618 M lib :040000 040000 fa9a72d3eaae021b0015e43bbe64a9cf7eadf46e 7b6821dd22bee36c808e4680a427aa3ec75faad0 M tests 09d2881!orm $> ```
admin commented 2026-01-22 14:43:59 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 8, 2015):

Comment created by hosiplan:

I've narrowed down the problem to this piece of code

<ins>            $fieldMapping = $class->fieldMappings[$columnName];
</ins>            if (isset($fieldMapping['declared']) && $fieldMapping['declared'] !== $class->name) {
<ins>                // Field was declared in a parent class, so we need to get the proper SQL table alias
</ins>                // for the joined parent table.
<ins>                $otherClassMetadata = $this->_em->getClassMetadata($fieldMapping['declared']);
</ins>                $sqlTableAliasForFieldAlias = $this->getSQLTableAlias($otherClassMetadata->getTableName(), $dqlAliasForFieldAlias);
+            }

This breaks the searchPatterns map

!https://dl.dropboxusercontent.com/u/32120652/permanent/DDC-3679.png!

Because it thinks it needs to use different table alias for mapped superclass that contains the id property.

@doctrinebot commented on GitHub (Apr 8, 2015): Comment created by hosiplan: I've narrowed down the problem to this piece of code ``` <ins> $fieldMapping = $class->fieldMappings[$columnName]; </ins> if (isset($fieldMapping['declared']) && $fieldMapping['declared'] !== $class->name) { <ins> // Field was declared in a parent class, so we need to get the proper SQL table alias </ins> // for the joined parent table. <ins> $otherClassMetadata = $this->_em->getClassMetadata($fieldMapping['declared']); </ins> $sqlTableAliasForFieldAlias = $this->getSQLTableAlias($otherClassMetadata->getTableName(), $dqlAliasForFieldAlias); + } ``` This breaks the searchPatterns map !https://dl.dropboxusercontent.com/u/32120652/permanent/[DDC-3679](http://www.doctrine-project.org/jira/browse/DDC-3679).png! Because it thinks it needs to use different table alias for mapped superclass that contains the `id` property.
admin commented 2026-01-22 14:44:00 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 8, 2015):

Comment created by hosiplan:

I've opened a pullrequest with failing test case https://github.com/doctrine/doctrine2/pull/1377

@doctrinebot commented on GitHub (Apr 8, 2015): Comment created by hosiplan: I've opened a pullrequest with failing test case https://github.com/doctrine/doctrine2/pull/1377
admin commented 2026-01-22 14:44:00 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 8, 2015):

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1377] was assigned:
https://github.com/doctrine/doctrine2/pull/1377

@doctrinebot commented on GitHub (Apr 8, 2015): Comment created by @doctrinebot: A related Github Pull-Request [GH-1377] was assigned: https://github.com/doctrine/doctrine2/pull/1377
admin commented 2026-01-22 14:44:00 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 8, 2015):

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1377] was labeled:
https://github.com/doctrine/doctrine2/pull/1377

@doctrinebot commented on GitHub (Apr 8, 2015): Comment created by @doctrinebot: A related Github Pull-Request [GH-1377] was labeled: https://github.com/doctrine/doctrine2/pull/1377
admin commented 2026-01-22 14:44:00 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Apr 9, 2015):

Comment created by @doctrinebot:

A related Github Pull-Request [GH-1377] was merged:
https://github.com/doctrine/doctrine2/pull/1377

@doctrinebot commented on GitHub (Apr 9, 2015): Comment created by @doctrinebot: A related Github Pull-Request [GH-1377] was merged: https://github.com/doctrine/doctrine2/pull/1377
admin commented 2026-01-22 14:44:00 +01:00
Author
Owner
Copy Link

@ostrolucky commented on GitHub (Aug 7, 2018):

Fix merged in https://github.com/doctrine/doctrine2/pull/1377

@ostrolucky commented on GitHub (Aug 7, 2018): Fix merged in https://github.com/doctrine/doctrine2/pull/1377
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#4520
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?