Convert SQL TO DQL ( doctrine)

Question

I want to convert SQL request TO DQL using Doctrine "Query Builder"

SELECT e1.id, e1.name, (SELECT COUNT() FROM (SELECT id FROM element e2 WHERE 
        e2.parent_id = e1.id LIMIT 1000) as e3  )

FROM element e1
WHERE
    e1.parent_id = xxx
AND
    e1.element_type_id = xxx

is it possible ?

Answer 1

You can write DQL for above SQL as below

SELECT e1.id, e1.name, COUNT(*) somealias
FROM YourBundle:Element e1
LEFT JOIN YourBundle:Element e2 WITH e1.id = e2.parent_id
WHERE e1.parent_id = xxx
AND e1.element_type_id = xxx
GROUP BY e1.id

I don't see the logic of using LIMIT in sub select.

It would be easier if you have defined the parent_id association in your entity as childs with i guess ManyToOne for unidirectional relation and doctrine will do the join part so you don't have to use WITH clause and your DQL will become like

SELECT e1.id, e1.name, COUNT(*) somealias
FROM YourBundle:Element e1
LEFT JOIN e1.childs e2
WHERE e1.parent_id = xxx
AND e1.element_type_id = xxx
GROUP BY e1.id