CODEWITHSUNDEEP
mysqlsymfony
LargeTuna
LargeTuna

Reputation: 2824

Syfmony Group By Error on Sub Select

In Symfony 2 I am attempting to do a sub select to grab a total count from a table. I am getting the following error each time: SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row. Why is the GROUP BY in my statement not working? 

$dql = " SELECT u.id as UserId, (
                          SELECT  count(mtp.id)
                          FROM    Site\MyBundle\Entity\MessageThread mtp
                          WHERE   mtp.status=0
                          GROUP BY mtp.message
                    ) as MessageThreadCountPending
                    FROM      Site\UserBundle\Entity\Entity\User u
                    WHERE     u.id=:id
                    ";

Example MessageThread Table

id | message | status | text 
-------------------------------------------
1  | 1       | 0      | Blah blah blah
2  | 2       | 0      | Blah blah blah
3  | 2       | 0      | Blah blah blah
4  | 3       | 0      | Blah blah blah

Desired result would by 3

Thanks for feedback!

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

One easy fix is to get rid of the group by in the subquery:

SELECT u.id as UserId,
       (SELECT count(mtp.id)
        FROM Site\MyBundle\Entity\MessageThread mtp
        WHERE mtp.status = 0
       ) as MessageThreadCountPending
FROM Site\UserBundle\Entity\Entity\User u
WHERE u.id = :id;

Although this fixes the symptom, I suspect that you want some sort of correlation clause in the subquery. I would guess something like this:

        WHERE mtp.status = 0 and mtp.user_id = u.id

Upvotes: 1

Related Questions