doctrine - Selecting from subquery in DQL

Question

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

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

The problem I am running into is that it complains that

Error: Class '(' is not defined

The actual DQL that produces that error is:

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 reiterate, how can I perform a selection from a sub query?

Answer 1

Using DQL I'm pretty sure that's not possible, but if you really need it you might want to check:

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

It's much more complex but it also gives you the freedom to send native query and execute it (the tricky part for me was object hydration).

On the other hand, if the last code segment resembles of anything what you're trying to achieve, there is a simpler way that requires no sub-queries:

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...