Order By, Group By - Problems

Question

Ok, so here's the issue. Currently I'm using the symfony framework to develop a web app. I'm using MySQL in the backend but want to keep my queries in their doctrine format to avoid any problems if I ever need to change databases.

In one section of the web application I'm looking for the last comment associated by any distinct user, sorted by the date. There can be multiple comments from many users all on the same date and time, but this query should only grab the last comment based on the date.

 comments table
 -----------------------------------------------------------------
 | comment_id  |  user_id   | comment      |  date_time          |
 -----------------------------------------------------------------
 |    1        |    2       |  Test_1      | 2010-01-01 00:00:00 |
 |    2        |    2       |  Test_2      | 2010-02-01 00:00:00 |
 |    3        |    2       |  Test_3      | 2010-03-01 00:00:00 |
 |    4        |    4       |  Test_4      | 2010-04-01 00:00:00 |
 |    5        |    4       |  Test_5      | 2010-05-01 00:00:00 |
 |    6        |    4       |  Test_6      | 2010-06-01 00:00:00 |
 |    7        |    3       |  Test_7      | 2010-07-01 00:00:00 |
 |    8        |    2       |  Test_8      | 2010-08-01 00:00:00 |
 |    9        |    3       |  Test_9      | 2010-09-01 00:00:00 |
 -----------------------------------------------------------------

Above is an example table. The query I run should return the data as follows.

 -----------------------------------------------------------------
 | comment_id  |  user_id   | comment      |  date_time          |
 -----------------------------------------------------------------
 |    9        |    3       |  Test_9      | 2010-09-01 00:00:00 |
 |    8        |    2       |  Test_8      | 2010-08-01 00:00:00 |
 |    6        |    4       |  Test_6      | 2010-06-01 00:00:00 |
 -----------------------------------------------------------------

The least amount of sub queries would obviously be better. It would be possible that the date_time could be lower on a future comment by a user.

It seems simple but it is proving rather difficult...

Answer 1

Unfortunately I don't know Doctrine syntax but I can give you a solution in SQL. This SQL query is tested in MySQL but should also work in many other databases:

SELECT comment_id, user_id, comment, date_time
FROM comments
WHERE comment_id IN (
    SELECT MAX(comment_id) AS comment_id
    FROM comments T1
    JOIN
    (
        SELECT user_id, MAX(date_time) AS date_time
        FROM comments
        GROUP BY user_id
    ) T2
    ON T1.user_id = T2.user_id AND T1.date_time = T2.date_time
    GROUP BY T1.user_id, T2.date_time
)

If someone else here wants to write an answer for doctrine they are welcome to use this as a starting point if it is of any help.

Answer 2

I don't know about doctrine or MySQL, but using a tuple in an IN clause may do the trick:

select * from comments
where user_id, comment_id in
 (select user_id, max(comment_id)
  from comments
  group by user_id)