CODEWITHSUNDEEP
mysqlsql
Dan
Dan

Reputation: 12096

Order by ignored when using group by on joined tables

I'm trying to write 1 query to display a list of forum threads ordered by the latest post date.

This is quite a simple process getting the data from the database however due to the joins and group by, the order by I wish to use isn't possible.

Table Structures

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        |
|  5   | A post 5  |  2               |  19/02/2015 |  1        |


Desired Output

This is the output I'd ideally have, the threads are sorted by the most recent thread post.

|  Thread Title |  Last Post User  | Last Post Time  |  Creator User  |
|---------------|------------------|-----------------|----------------| 
|  Thread 2     |  1               |  19/02/2015     |  2             |
|  Thread 1     |  2               |  18/02/2015     |  1             |


The Problem

At the moment I can get everything in 1 query however there is an issue when trying to ORDER BY ForumPosts.id, I think this is due to the GROUP BY combined with the joins.

This means I have all of the required data but it's not in the order of the latest forum post.

Current incorrect query

SELECT 
  ForumThreads.title, 
  SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.id ASC), ',', 1),
  MAX(ForumPosts.`post_time`),
  SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.id DESC), ',', 1)
FROM
  forumThreads 
  INNER JOIN `ForumRooms` 
    ON ForumThreads.forum_room_id = ForumRooms.id 
  INNER JOIN `ForumPosts` 
    ON ForumThreads.id = ForumPosts.`forum_thread_id` 
WHERE ForumRooms.id = 1 
GROUP BY ForumThreads.id 
ORDER BY ForumPosts.id DESC

How can I write a query that will achieve the above desired output?

Update 1

I've tried implementing the sub query approach as suggested by Jean-François Savard but the ORDER BY a.id is ignored and it still orders by ForumTopics.id

Upvotes: 1

Views: 71

Answers (3)

TehShrike
TehShrike

Reputation: 10074

Only a small change to the original query is needed - either add MAX(ForumPosts.id) AS max_post_id to your SELECT and then order by that:

SELECT 
  ForumThreads.title, 
  SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.id ASC), ',', 1),
  MAX(ForumPosts.`post_time`),
  SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.id DESC), ',', 1),
  MAX(ForumPosts.id) AS max_post_id
FROM
  forumThreads 
  INNER JOIN `ForumRooms` 
    ON ForumThreads.forum_room_id = ForumRooms.id 
  INNER JOIN `ForumPosts` 
    ON ForumThreads.id = ForumPosts.`forum_thread_id` 
WHERE ForumRooms.id = 1 
GROUP BY ForumThreads.id 
ORDER BY max_post_id DESC

Or you can just alias MAX(ForumPosts.post_time) and order by that:

SELECT 
  ForumThreads.title, 
  SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.id ASC), ',', 1),
  MAX(ForumPosts.`post_time`) AS max_post_time,
  SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.id DESC), ',', 1)
FROM
  forumThreads 
  INNER JOIN `ForumRooms` 
    ON ForumThreads.forum_room_id = ForumRooms.id 
  INNER JOIN `ForumPosts` 
    ON ForumThreads.id = ForumPosts.`forum_thread_id` 
WHERE ForumRooms.id = 1 
GROUP BY ForumThreads.id 
ORDER BY max_post_time DESC

Upvotes: 1

aimstone
aimstone

Reputation: 106

You can use this:

SELECT a.ThreadTitle, a.LastPostUser, a.LastPostTime, p2.user_id CreatorUser FROM (
    SELECT t.title ThreadTitle, p.user_id LastPostUser, p.post_time LastPostTime, p.forum_thread_id
    FROM (SELECT * FROM ForumPosts ORDER BY post_time DESC) p
    JOIN ForumThreads t ON t.id=p.forum_thread_id
    JOIN ForumRooms r ON t.forum_room_id=r.id
    GROUP BY threadTitle ) a
JOIN (SELECT * FROM ForumPosts GROUP BY forum_thread_id) p2 ON p2.forum_thread_id=a.forum_thread_id 
ORDER BY 3 DESC

Upvotes: 1

Jean-François Savard
Jean-François Savard

Reputation: 21004

You could use a subquery

select * from(
SELECT 
  ForumPosts.id,
  group_concat(ForumThreads.title), 
  SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.id ASC), ',', 1),
  MAX(ForumPosts.`post_time`),
  SUBSTRING_INDEX(GROUP_CONCAT(ForumPosts.user_id ORDER BY ForumPosts.id DESC), ',', 1)
FROM
  forumThreads 
  INNER JOIN `ForumRooms` 
    ON ForumThreads.forum_room_id = ForumRooms.id 
  INNER JOIN `ForumPosts` 
    ON ForumThreads.id = ForumPosts.`forum_thread_id` 
WHERE ForumRooms.id = 1 
GROUP BY ForumThreads.id) a
ORDER BY a.id DESC

Upvotes: 1

Related Questions