Select from a subquery in DQL

Question

I would like to perform a SELECT from the results of a subquery in DQL. This is equivalent to doing the following in SQL:

SELECT * FROM ( SELECT foo1,foo2 FROM bar ) where foo1='something'; 

The problem I am facing is that she complains that

 Error: Class '(' is not defined 

The actual DQL that produces this error:

 SELECT u FROM ( SELECT u, COUNT(u) as total FROM Utterance u LEFT JOIN u.recordings r WHERE r.speaker IS NULL OR r.speaker <> 5 GROUP BY u.id ) matched WHERE total < 5 

So, to repeat, how can I make a selection from a subquery?

Answer 1

Using DQL, I'm sure this is not possible, but if you really need it, you can check:

Doctrine Native SQL . ( examples , permalink from the same page)

This is much more complicated, but also gives you the freedom to send your own request and execute it (the difficult part for me is hydrating the object).

On the other hand, if the last code segment is similar to what you are trying to achieve, there is an easier way that does not require subqueries:

 SELECT u FROM Utterance u LEFT JOIN u.recordings r WHERE r.speaker IS NULL OR r.speaker <> 5 GROUP BY u.id HAVING COUNT(u) < 5 

Hope this helps ...