CODEWITHSUNDEEP
subquerydoctrine-orm
Tom
Tom

Reputation: 61

Doctrine2 subquery

I'm trying to write a subquery in doctrine2, to sort a table ordered by date column of another table.

(let's say I'm querying table A, which has an id column, and B has an a_id and a date, in the subquery b.a_id = a.id)

I'm using query builder, and the addSelect method, but since I can't use LIMIT in my query I get this error:

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

This error is true, but how could I limit this query to 1 row, if LIMIT is not allowed in doctrine2 and when I try to do it by querybuilder (I mean the subquery) and I'm using setMaxResults, and then getDQl it is still not working.

->addSelect('(SELECT b.date FROM B b WHERE b.conversation = a.id ORDER BY b.date DESC)')

Is there any solution for my problem?

Thanks

Upvotes: 1

Views: 2089

Answers (1)

Vladislav Rastrusny
Vladislav Rastrusny

Reputation: 29975

Make the query return exactly one row. Try SELECT MAX(b.date) FROM B b WHERE b.conversation = a.id)

Upvotes: 1

Related Questions