在 DQL 中加入并计数

我有一个 MySQL 命令，但在 DQL 中找不到等效命令。我正在尝试获取评论最多的帖子列表。这是 MySQL 命令：

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

结果如下：

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

在 DQL 中，它应该是：

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

但这给出了：

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

我不明白 50 从何而来，以及为什么两个结果之间存在差异。你能告诉我如何让这个连接在 Doctrine 中工作吗？

我做了一些测试，发现一切似乎都很好。

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

数据库模式非常简单，我认为不需要任何解释。

#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 entire Video object instead of its id (v instead of v.id in SELECT clause) the query will execute as well. Of course instead of id element there will be an Video object under 0^th element.

在 Dotrine 2.1.0-DEV 上进行测试