Limit number of root entities in Doctrine query, but allowing any number of joined entities

Question

For example: I have Articles and Comments.

How do I select only 10 top articles and all comments for them with a single DQL query?

Doing:

SELECT A, C
FROM Article A
LEFT JOIN A.comments C

and setMaxResults(10) will limit not the articles alone, but articles+comments. So as a result I will get, e.g.: first article and 7 comments for it and second article and 3 comments for it.

But I want to receive all 10 first articles and all comments for them.

In plain SQL query it will be:

SELECT * FROM (
    SELECT * FROM articles
    LIMIT 10
) AS A
LEFT JOIN comments C ON C.article_id = A.id

Is is possible with Doctrine? Or do I need to use native queries?

Thank you!

Answer 1

I wasn't being able to find solution to this problem, so I decided to change strategy. Maybe someone will find it useful in he's situation.

I decided to split this query in two. So I'm using one query to select IDs for a limited subset of root entities and another to actually fetch whole tree by specifying retrieved IDs as a condition.

Example:

SELECT A.id
FROM Article A
LIMIT 10

SELECT A, C
FROM Article A
LEFT JOIN A.comments C
WHERE A.id IN (4, 8, 15, 16, 23, 42, ...)