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?

Raffle

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

Sweven

my final query needed to be a little bit more complicated than that, although I didn't know you could use HAVING like that so thanks for that tip! In the end I went with the Native SQL and object hydration. Thanks! – Raffle

@drewag, and your final result with native query will be very interesting if shared here. (I know it is been almost 4 years past but...) thanks – Siddon

Did you manage to solve your problem with object hydration in a clean way? – Alternation