6

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 ?

Elnur Abdurrakhimov
  • 44,533
  • 10
  • 148
  • 133
Quentin
  • 2,529
  • 2
  • 26
  • 32
  • What's the exact query generated by Doctrine? You can easily check it using Symfony's WDT. Also you should store total number of comments in `Post` table and in/decrement it whenever you add/remove a comment. – Crozin Jun 13 '11 at 23:36
  • Hi Crozin, it is what I am doing actually. But I want to make this work because I want to add a statement like `WHERE post.date > ?` to get the "hotest" posts for last 24h, last day, last month... I am running these commands with `app/console doctrine:query:dql` and `app/console doctrine:query:sql`. – Quentin Jun 14 '11 at 01:07

2 Answers2

8

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

Tested on Doctrine 2.1.0-DEV

Crozin
  • 43,890
  • 13
  • 88
  • 135
0

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 were grouping by post.id, not post

btw, this is a total guess. I don't know DQL, but I do know hibernate, and it seems similar-ish. If it's wrong I'll delete this answer - let me know by comment.

Bohemian
  • 412,405
  • 93
  • 575
  • 722