Join and count in DQL

Question

I have a MySQL command and I cannot find the equivalent in DQL. I am trying to get a list of the most commented posts. Here is the MySQL command:

SELECT posts.id, COUNT(comments.id) AS num FROM posts LEFT JOIN comments ON ( posts.id = comments.post_id ) GROUP BY posts.id 

Here is the result:

 id num 1 8 2 9 3 17 4 7 5 6 6 20 7 7 8 10 9 14 10 7 

In DQL, it should be:

 SELECT post, COUNT(comment.id) AS num FROM Entity\Post post LEFT JOIN post.comments comment GROUP BY post.id 

But it gives:

 id num 1 50 2 0 3 0 4 0 5 0 6 0 7 0 8 0 9 0 10 0 

I do not understand where 50 comes from and why there is a difference between the two results. Could you tell me how to make this work in Doctrine?

Answer 1

I did some tests, and I found that everything seemed perfect.

 Video: id, title, ... Comment: id, video_id, content, ... 

The database schema is very simple, and I think there is no explanation.

 #DQL: SELECT v.id, COUNT(c.id) AS num FROM Video v JOIN v.comments c GROUP BY v.id ORDER BY num DESC #Generated SQL: SELECT v0_.id AS id0, COUNT(v1_.id) AS sclr1 FROM video v0_ INNER JOIN video_comment v1_ ON v0_.id = v1_.video_id GROUP BY v0_.id ORDER BY sclr1 DESC #Result set: Array ( [0] => Array ( [id] => 148 [num] => 3 ) [1] => Array ( [id] => 96 [num] => 2 ) [2] => Array ( [id] => 111 [num] => 1 ) [3] => Array ( [id] => 139 [num] => 1 ) ) 

If you select the entire Video object instead of your id ( v instead of v.id in SELECT ), the query will also be executed. Of course, instead of the id element, there will be a Video object under the 0 ^th element.

Tested on Doctrine 2.1.0-DEV

Answer 2

EDITED . People. This answer does not work. At least you can eliminate this from possible solutions.

 SELECT post, COUNT(comment.id) AS num FROM Entity\Post post LEFT JOIN post.comments comment GROUP BY post 

You grouped post.id , not post

btw, this is a common guess. I don't know DQL, but I know hibernate, and it looks like-ish. If this is not the case, I will delete this answer - let me know by the comments.

Answer 3

I do not know DQL, and it can be silly .... but you posted this:

 SELECT post, COUNT(comment.id) AS num FROM Entity\Post post LEFT JOIN post.comments comment GROUP BY post.id 

Don't you choose post.id ? I know this sounds strange, but from what I understand, select post equivalent to select * . However, in your release you only get the identifier column. I thought it was worth noting (even if it is not).

/ prepares for downvote