CODEWITHSUNDEEP
mysqlsql
Dan
Dan

Reputation: 12096

MySQL - Order by on multiple left join

So I have the tables ForumRooms, ForumTopics and ForumPosts, they're pretty self explanatory.

At the moment I'm changing the query for the main forum rooms page to be able to get the last post time and user, stored in ForumPosts.


Table Structure

ForumRooms

|  id  |  title  |
|----------------|
|  1   | Room 1  |
|  2   | Room 2  |

ForumThreads

|  id  |  title    |  forum_room_id  |
|------------------|-----------------|
|  1   | Thread 1  |  1              |
|  2   | Thread 2  |  2              |
|  3   | Thread 3  |  1              |

ForumPosts

|  id  |  content  |  forum_thread_id |  post_time  |  user_id  |
|------------------|------------------|-------------|-----------|
|  1   | A post 1  |  1               |  15/02/2015 |  1        |
|  2   | A post 2  |  2               |  16/02/2015 |  2        |
|  3   | A post 3  |  1               |  17/02/2015 |  1        |
|  4   | A post 4  |  1               |  18/02/2015 |  2        |


The Aim

I'm trying to write 1 query to allow the following formatted output:

Output

|  ForumRoom  |  Thread Count  |  Post Count  |  Last Post Time  |  Last Post UserID  |
|-------------|----------------|--------------|------------------|--------------------|
|  Room 1     |  2             |  3           |  16/02/2015      |  2                 |
|  Room 2     |  1             |  1           |  18/02/2015      |  2                 |


The Problem

At the moment I can get everything in 1 query apart from the Last Post Time and Last Post UserID.

The below query should give what I need due to the ORDER BY on the ForumPosts.id but I think the GROUP BY prevents it from working as required.

SELECT ForumRooms.title, COUNT(DISTINCT ForumThreads.id), COUNT(ForumPosts.id), ForumPosts.post_time, ForumPosts.user_id
FROM ForumRooms
LEFT JOIN ForumThreads ON ForumRooms.id = ForumThreads.forum_room_id
LEFT JOIN ForumPosts ON ForumThreads.id = ForumPosts.forum_thread_id
GROUP BY ForumRooms.id
ORDER BY ForumPosts.id DESC

How can I adapt my query to get the last two missing pieces of data?

Upvotes: 1

Views: 162

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

This is probably a good place to use the substring()/group_concat() trick:

SELECT ForumRooms.title, COUNT(DISTINCT ForumThreads.id), COUNT(ForumPosts.id), 
       MAX(ForumPosts.post_time),
       SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.post_time DESC), ',', 1)
FROM ForumRooms LEFT JOIN
     ForumThreads
     ON ForumRooms.id = ForumThreads.forum_room_id LEFT JOIN
     ForumPosts
     ON ForumThreads.id = ForumPosts.forum_thread_id
GROUP BY ForumRooms.id
ORDER BY ForumPosts.id DESC

Upvotes: 3

Related Questions