How to make a nested query in Doctrine DQL?

Question

symfony

Good afternoon.
You need a query like this:

SELECT name,
       (SELECT id
        FROM tree t2
        WHERE t2.left_key < t1.left_key AND t2.right_key > t1.right_key
        ORDER BY t2.right_key-t1.right_key ASC LIMIT 1) AS parent
FROM tree t1
WHERE t1.left_key >= 1 AND t1.right_key <= 6
ORDER BY right_key-left_key DESC

turn it into DQL. Either in NativeSQL, but with the correct ResultMapping.
It fails with DQL due to LIMIT which doesn't work. And setMaxResults() doesn't work for nested queries.
And with NativeSQL it doesn't work because of ResultMapping.
The Tree entity has the following fields: id, name, left_key, right_key, level.
The query should return a table with a parent column that represents the id of the parent.

Answer 1

Perhaps you should pay attention to the ready-made implementation of trees for Doctrine? There is also an integration for this package in Symfony. This will allow you not to reinvent the wheel, but to use a ready-made, proven and very popular solution, freeing up time from writing and supporting your implementation for other tasks.