在 DQL 中加入和计数

2022-01-16 00:00:00 mysql symfony doctrine doctrine-orm dql

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

I have a MySQL command and I cannot find the equivalent in DQL. I am trying to fetch the list 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

结果如下:

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

在 DQL 中，应该是:

In DQL, it should be :

SELECT post, COUNT(comment.id) AS num
FROM EntityPost 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 的来源以及为什么 2 个结果之间存在差异.你能告诉我如何让这个加入在 Doctrine 中工作吗?

I don't understand where the 50 comes from and why there is a difference between the 2 results. Could you tell me how to make this join work in Doctrine ?

推荐答案

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

I've made a few test and I found that everything seems to be fine.

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

数据库架构很简单，我觉得不用解释了.

Database schema is very simple and I think there's no need for any 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
            )

    )

如果您选择整个 Video 对象而不是其 ID(v 而不是 SELECT 中的 v.id子句)查询也将执行.当然，在 0th 元素下会有一个 Video 对象，而不是 id 元素.

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 0th element.

在 Doctrine 2.1.0-DEV 上测试

相关文章