Doctrine multi-row groupBy count

Question

I have the following Query with doctrine:

$q = Doctrine_Query::create()
    ->select("q.id, q.user_id, count(ua.id) as quantity")
    ->from("question q")
    ->leftJoin("q.Answers a")
    ->leftJoin("a.UserAnswers ua")
    ->groupBy("ua.user_id");

Generated SQL is:

SELECT q.id AS q__id, q.user_id AS q__user_id, COUNT(u.id) AS u__0 FROM question q LEFT JOIN answer a ON q.id = a.question_id LEFT JOIN user_answer u ON a.id = u.answer_id GROUP BY u.user_id

And the result of this query in MySQL is like:

+-------+------------+------+
+ q__id + q__user_id + u__0 +
+-------+------------+------+
+     1 +          1 +    0 +
+-------+------------+------+
+    26 +          6 +    2 +
+-------+------------+------+
+    26 +          6 +    1 +
+-------+------------+------+

Using Doctrine, there is a way to obtain "u__0" column values?

I tried:

$questions = $q->execute();
echo (count($questions));

but the result has only 2 rows (not 3).

How can I get "u__0" column values? (0, 2, 1) using Doctrine?

j0k · Answer 1 · 2013-03-14T09:21:15.727

2

You have to loop for each result and retrieve the quantity value:

$q = Doctrine_Query::create()
    ->select("q.id as id, q.user_id as question_user_id, ua.user_id as answer_user_id, count(ua.id) as quantity")
    ->from("question q")
    ->leftJoin("q.Answers a")
    ->leftJoin("a.UserAnswers ua")
    ->groupBy("answer_user_id");

$results = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
foreach ($results as $result)
{
    var_dump($result['quantity']);
}

edited Mar 14 '13 at 09:21

answered Mar 13 '13 at 15:26

j0k

22,600
28
79
90

My question is, using doctrine i only get 2 results (In plain SQL i get 3 results), i think Doctrine is "grouping" two last results in one. – Pipe Mar 13 '13 at 18:49
@Pipe oh I see! Sorry I misunderstood, I've updated my answer. Maybe, if you force doctrine to use alias to group by, it will work. By the way, I think you have to add ua.user_id in the select. – j0k Mar 14 '13 at 09:20
nop, it throws error:The left side of the join between the aliases 'a' and 'ua' must have at least the primary key field(s) selected. – Pipe Mar 14 '13 at 13:39
Can you provide the revelant part of your schema? So I can give it a try – j0k Mar 14 '13 at 13:55
I found a solution, changing my SQL... I updated my question... thanks :) – Pipe Mar 14 '13 at 14:13
@Pipe haha nice :) Instead of updating your question, you should post an answer and accept it – j0k Mar 14 '13 at 14:15
ah ok, re-updating my question and creating an answer... :) – Pipe Mar 14 '13 at 18:06

score 1 · Accepted Answer · answered Mar 14 '13 at 18:07

I changed SQL query to:

$q = Doctrine_Query::create()
    ->select("q.id, q.user_id, a.id, count(ua.id) as quantity")
    ->from("Answer a")
    ->leftJoin("a.Question q")
    ->leftJoin("a.UserAnswers ua")
    ->groupBy("ua.user_id");

And it works ;)

Doctrine multi-row groupBy count

2 Answers2