[Bug] ORDER BY and LIMIT using subqueries #5960

New Issue

admin · 2026-01-22T15:23:11+01:00

admin commented

2026-01-22 15:23:11 +01:00

Originally created by @joshlopes on GitHub (May 2, 2018).

Hello!

I'm trying to upgrade 2.4.8 to 2.5.14 and i've been having some issues using ORDER BY and LIMIT using 1 to Many JOINs.

The follow query will run normally in my mysql client:

SELECT distinct(p.id) 
FROM product p 
INNER JOIN product_translations pt ON pt.id = p.id AND pt.lang = 'en' # this is just one record
ORDER BY pt.name 
GROUP BY p.id DESC 
LIMIT 20;

product has a 1 to many relation with product_translations and this works fine on 2.4 and below from 2.5 everytime i try to run that same query i get a

Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers.

This was a change done as part of this PR: https://github.com/doctrine/doctrine2/pull/1353 more precisely this file: 608dfa2f57 (diff-e7afd03e36cf8d3fe30efd893375da43)

Looking around i'm not sure, but i thin KNP Paginator is even using the old one: https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/Query/LimitSubqueryWalker.php as a work around for the issue.

Originally created by @joshlopes on GitHub (May 2, 2018). Hello! I'm trying to upgrade 2.4.8 to 2.5.14 and i've been having some issues using ORDER BY and LIMIT using 1 to Many JOINs. The follow query will run normally in my mysql client: ```mysql SELECT distinct(p.id) FROM product p INNER JOIN product_translations pt ON pt.id = p.id AND pt.lang = 'en' # this is just one record ORDER BY pt.name GROUP BY p.id DESC LIMIT 20; ``` `product` has a 1 to many relation with `product_translations` and this works fine on 2.4 and below from 2.5 everytime i try to run that same query i get a > Cannot select distinct identifiers from query with LIMIT and ORDER BY on a column from a fetch joined to-many association. Use output walkers. This was a change done as part of this PR: https://github.com/doctrine/doctrine2/pull/1353 more precisely this file: https://github.com/doctrine/doctrine2/commit/608dfa2f571f2b93ccdb2fcd9ab66c4160f02a9e#diff-e7afd03e36cf8d3fe30efd893375da43 Looking around i'm not sure, but i thin KNP Paginator is even using the old one: https://github.com/KnpLabs/knp-components/blob/master/src/Knp/Component/Pager/Event/Subscriber/Paginate/Doctrine/ORM/Query/LimitSubqueryWalker.php as a work around for the issue.

admin commented

2026-01-22 15:23:12 +01:00

@stollr commented on GitHub (Oct 18, 2018):

Why do you need the distinct? The GROUP BY already results in one row per product.

Changing the SELECT distinct(p.id) to SELECT p.id should give you the same result.

@stollr commented on GitHub (Oct 18, 2018): Why do you need the `distinct`? The `GROUP BY` already results in one row per product. Changing the `SELECT distinct(p.id)` to `SELECT p.id` should give you the same result.

admin commented

2026-01-22 15:23:13 +01:00

@joshlopes commented on GitHub (Oct 19, 2018):

@naitsirch yes you are right - although the problem has nothing to do with distinct i think. It's with LIMIT and ORDER.

@joshlopes commented on GitHub (Oct 19, 2018): @naitsirch yes you are right - although the problem has nothing to do with distinct i think. It's with LIMIT and ORDER.

admin referenced this issue

2026-01-22 15:45:53 +01:00

Avoid Using unqualifed COUNT in EntityPersister #7163

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5960