Limit in DQL subquery #7291

New Issue

admin · 2026-01-22T15:49:22+01:00

admin commented

2026-01-22 15:49:22 +01:00

Originally created by @Guervyl on GitHub (Dec 29, 2023).

Bug Report

Q	A
BC Break	yes
Version	2.12

Summary

I want to be able to limit a subquery but doctrine is not adding the limit. I know from an issue you said to fall to SQL native but, the problem is I have to deal with ResultSet mapping.

$categoryQuery = $this->_em->createQuery("SELECT c FROM App\Entity\FbShareGroupCategory c WHERE c.id = g.id")
            ->setMaxResults(1)
            ->getDQL();

        return $this->_em->createQuery("SELECT g, ( $categoryQuery) FROM App\Entity\FbGroups g")
            ->setMaxResults(2)
            ->getResult();

Current behavior

It does not add the limit into the $categoryQuery DQL.

How to reproduce

Use the above DQL code.

Expected behavior

It was supposed to add the Limit to the DQL.

Originally created by @Guervyl on GitHub (Dec 29, 2023). ### Bug Report | Q | A |------------ | ------ | BC Break | yes | Version | 2.12 #### Summary I want to be able to limit a subquery but doctrine is not adding the limit. I know from an issue you said to fall to SQL native but, the problem is I have to deal with *ResultSet mapping*. ``` $categoryQuery = $this->_em->createQuery("SELECT c FROM App\Entity\FbShareGroupCategory c WHERE c.id = g.id") ->setMaxResults(1) ->getDQL(); return $this->_em->createQuery("SELECT g, ( $categoryQuery) FROM App\Entity\FbGroups g") ->setMaxResults(2) ->getResult(); ``` #### Current behavior It does not add the limit into the `$categoryQuery` DQL. #### How to reproduce Use the above DQL code. #### Expected behavior It was supposed to add the Limit to the DQL.

admin closed this issue

2026-01-22 15:49:22 +01:00

admin commented

2026-01-22 15:49:22 +01:00

@greg0ire commented on GitHub (Dec 30, 2023):

It was supposed to add the Limit to the DQL.

Not really, no… I mean, I'm sure you expected it, but I don't know why you expected it: as soon as you provide the DQL in one way (first argument of createQuery), and the limit/offset another way, I don't know why you expect getDQL() to provide more, or setMaxResults() to perform some kind of merge. If what you expected was possible, then there would be LIMIT and OFFSET keywords directly in DQL.

@greg0ire commented on GitHub (Dec 30, 2023): > It was supposed to add the Limit to the DQL. Not really, no… I mean, I'm sure you expected it, but I don't know why you expected it: as soon as you provide the DQL in one way (first argument of `createQuery`), and the limit/offset another way, I don't know why you expect `getDQL()` to provide more, or `setMaxResults()` to perform some kind of merge. If what you expected was possible, then there would be `LIMIT` and `OFFSET` keywords directly in DQL.

admin commented

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

@Guervyl commented on GitHub (Dec 30, 2023):

hen there would be LIMIT and OFFSET keywords directly in DQL.
Is there Limit in DQL? How to set it? Because I get error when I set SELECT s FROM MyClass s LIMIT 1.

@Guervyl commented on GitHub (Dec 30, 2023): > hen there would be LIMIT and OFFSET keywords directly in DQL. Is there `Limit` in DQL? How to set it? Because I get error when I set `SELECT s FROM MyClass s LIMIT 1`.

admin commented

2026-01-22 15:49:24 +01:00

@greg0ire commented on GitHub (Dec 30, 2023):

Ok, let me add some emphasis here:

If what you expected was possible, then there would be LIMIT and OFFSET keywords directly in DQL.

I think you're getting a syntax error, because DQL currently has no such keywords.

@greg0ire commented on GitHub (Dec 30, 2023): Ok, let me add some emphasis here: _If_ what you expected was possible, then there _would_ be `LIMIT` and `OFFSET` keywords directly in DQL. I think you're getting a syntax error, because DQL currently has no such keywords.

admin commented

2026-01-22 15:49:24 +01:00

@Guervyl commented on GitHub (Jan 2, 2024):

Hi there,

Maybe I'm missing a DQL feature, but this DQL SELECT g FROM App\Entity\FbGroups g LIMIT 1 throws [Syntax Error] line 0, col 36: Error: Expected end of string, got 'LIMIT'.

Why is this important to have limit? Because I want this query in DQL:

SELECT g.*, c.*, p.* FROM `fb_groups` g
JOIN fb_share_group_category c ON c.id = (SELECT gc.category_id FROM fb_share_group_category_group gc WHERE gc.fb_group_id = g.id LIMIT 1)
JOIN fb_share_group_post p ON p.id = (SELECT p2.id FROM fb_share_group_post p2 WHERE p2.category_id = c.id LIMIT 1);

I could use Join but, this is different result:

SELECT g.*, c.*, p.* FROM `fb_groups` g
JOIN fb_share_group_category_group gc ON gc.fb_group_id = g.id
JOIN fb_share_group_category c ON c.id = gc.category_id
JOIN fb_share_group_post p ON p.category_id = gc.category_id
LIMIT 1

I want to list all groups. But with no repetitions. So using subquery to limit the query so that I can even order by is my only choice. And I can't reproduce it using DQL.

@Guervyl commented on GitHub (Jan 2, 2024): Hi there, Maybe I'm missing a DQL feature, but this DQL `SELECT g FROM App\Entity\FbGroups g LIMIT 1` throws `[Syntax Error] line 0, col 36: Error: Expected end of string, got 'LIMIT'`. Why is this important to have limit? Because I want this query in DQL: ``` SELECT g.*, c.*, p.* FROM `fb_groups` g JOIN fb_share_group_category c ON c.id = (SELECT gc.category_id FROM fb_share_group_category_group gc WHERE gc.fb_group_id = g.id LIMIT 1) JOIN fb_share_group_post p ON p.id = (SELECT p2.id FROM fb_share_group_post p2 WHERE p2.category_id = c.id LIMIT 1); ``` I could use Join but, this is different result: ``` SELECT g.*, c.*, p.* FROM `fb_groups` g JOIN fb_share_group_category_group gc ON gc.fb_group_id = g.id JOIN fb_share_group_category c ON c.id = gc.category_id JOIN fb_share_group_post p ON p.category_id = gc.category_id LIMIT 1 ``` I want to list all groups. But with no repetitions. So using subquery to limit the query so that I can even `order by` is my only choice. And I can't reproduce it using DQL.

admin commented

2026-01-22 15:49:24 +01:00

@greg0ire commented on GitHub (Jan 2, 2024):

Maybe I'm missing a DQL feature, but this DQL SELECT g FROM App\Entity\FbGroups g LIMIT 1 throws [Syntax Error] line 0, col 36: Error: Expected end of string, got 'LIMIT'.

Yes. It does. Because, and this is key, that feature does not exist. There is no LIMIT keyword in DQL for now.

@greg0ire commented on GitHub (Jan 2, 2024): > Maybe I'm missing a DQL feature, but this `DQL SELECT g FROM App\Entity\FbGroups g LIMIT 1` throws `[Syntax Error] line 0, col 36: Error: Expected end of string, got 'LIMIT'.` Yes. It does. Because, and this is key, that feature does _not_ exist. There is no LIMIT keyword in DQL for now.

admin commented

2026-01-22 15:49:24 +01:00

@Guervyl commented on GitHub (Jan 3, 2024):

So, My question was about this. It is impossible to set limit in DQL while limit is a very crucial feature in SQL.

@Guervyl commented on GitHub (Jan 3, 2024): So, My question was about this. It is impossible to set limit in DQL while `limit` is a very crucial feature in SQL.

admin commented

2026-01-22 15:49:24 +01:00

@greg0ire commented on GitHub (Jan 3, 2024):

I asked internally, and to me it's not fully clear why:

@greg0ire commented on GitHub (Jan 3, 2024): I asked internally, and to me it's not fully clear why: ![2024-01-03_14-39](https://github.com/doctrine/orm/assets/657779/e67f82e1-1c80-40f2-be30-9e2fd35766b7)

admin referenced this issue

2026-01-22 16:07:26 +01:00

[PR #7291] [2.6] Fix for #7068: EntityManager::find() with pessimistic lock should check for transaction #10406

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#7291