CODEWITHSUNDEEP
mysqlforumgreatest-n-per-group
SqlSoul
SqlSoul

Reputation: 19

MySQL Query: Getting the most recent forum post

I'm doing some work for a department that has a pretty basic table structure set up for their forums in a MySQL database:

Each post belongs to a thread and each thread belongs to a category. There is also a users table for author information.

TABLE category:
id (int)
name (varchar)

TABLE thread:
id (int)
category_id (int)
user_id ((int)
title (varchar)
last_post (int) -- logs a unix timestamp of the last posts insertion

TABLE post:
id (int)
user_id (int)
thread_id (int)
post (longtext)
timestamp (int)

TABLE users:
id (int)
username (varchar)

I'm trying to create a page that will display all threads a user has posted a message in (only once, so if the user has posted int the thread 3 times it should still show up only once in the list) and the most recent post added to each of those threads.

So these are the fields I'm trying to SELECT:
Category Name
Category Id
Thread Title
Thread ID
The message id of the most recently added message to that thread
The message text of the most recently added message to that thread
The author's user id of the most recently added message to that thread
The author's username of the most recently added message to that thread
The time the most recently added message was posted

It could be ordered by thread's most recent activity.

Is this possible in one query? Obviously a way to simplify this would be to query all threads a user has posted a message in and then send a separate query for each one to get the most recently added post info..

Thanks!

Upvotes: 0

Views: 1020

Answers (1)

fancyPants
fancyPants

Reputation: 51908

Untested, but that should do it:

SELECT DISTINCT
c.name
, c.id
, t.title
, t.id
, p.id
, p.post
, u.id
, u.username
, FROM_UNIXTIME(p.`timestamp`) AS postDate
FROM
category c
INNER JOIN thread t ON t.category_id = c.id
INNER JOIN post p ON p.thread_id = t.id
INNER JOIN users u ON u.id = p.user_id
INNER JOIN users u2 ON u2.id = p.user_id
WHERE
u2.id = userID_whose_threads_are_to_display
AND p.timestamp = (SELECT MAX(`timestamp`) FROM post WHERE post.thread_id = p.thread_id)
ORDER BY postDate DESC

Note that it's imho a bad idea to name a column like a data type (timestamp in your case, that's why I put it in ``).

Upvotes: 1

Related Questions