加入并计入DQL [英] Join and count in DQL

问题描述

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

  SELECT posts.id，COUNT（comments.id）AS num 
 FROM posts 
 LEFT JOIN评论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中，应该是：

 code> SELECT post，COUNT（comment.id）AS num 
 FROM Entity\Post post 
 LEFT JOIN post.comments注释
 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中工作？

解决方案

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

 视频：id，title，... 
评论：id，video_id，content， ... 
  

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

  #DQL：
 SELECT v.id，COUNT（c.id）AS num 
 FROM视频v 
 JOIN v.comments c 
 GROUP BY v.id 
 ORDER BY num DESC 
 
＃生成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：
数组
（
 [0] =>数组
（
 [id] => 148 
 [num ] => 3 
）
 
 [1] => ; Array 
（
 [id] => 96 
 [num] => 2 
）
 
 [2] => Array 
（
 [id] => 111 
 [num] => 1 
）
 
 [3] => Array 
（
 [id] => 139 
 [num] => 1 
）
 
）
  

如果您选择整个视频对象而不是其id（ v 而不是 v.id 在 SELECT 子句中），查询也将执行。当然，除了 id 元素之外，在 ^th 元素之间将有一个视频对象。

测试了Doctrine 2.1.0-DEV

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

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 this gives :

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

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
            )

    )

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.

Tested on Doctrine 2.1.0-DEV

这篇关于加入并计入DQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！