MySQL Query, Join, and Myself, or how I always take the hard way through.

Question

I'm creating a small forum.

Attempting to run SElECT... JOIN... query too pick up information on the individual posts, plus the last reply (if any). As part of my desire to do everything the hard way, this covers five tables (only columns revelant to this issue are being stated)

commentInfo    referenceID | referenceType | authorID | create
postit         id | title
postitInfo     referencePostitID | create | authorID
user           id | username | permission
userInfo       referenceUserID | title

So, I run this query SELECT... JOIN... query to get the most recent topics and their last replies.

SELECT DISTINCT
    t1.id, t1.title, t2.create, t2.lastEdit, t2.authorID, t3.username, 
    t4.title AS userTitle, t3.permission, t5.create AS commentCreate, 
    t5.authorID AS commentAuthor, t6.username AS commentUsername, 
    t6.permission AS commentPermission
FROM rantPostit AS t1
LEFT JOIN (rantPostitInfo AS t2) 
    ON ( t1.id = t2.referencePostitID)
LEFT OUTER JOIN (rantUser as t3, rantUserInfo as t4) 
    ON (t2.authorId = t3.id AND t4.referenceUserId = t2.authorId)
LEFT OUTER JOIN (rantCommentInfo as t5, rantUser as t6) 
    ON (t5.referenceType = 8 AND t5.referenceID = t1.id AND t6.id = t5.authorID)
ORDER BY t2.create DESC, t5.create DESC

Now, this returns the topic posts. Say I have two of them, it returns both of them fine. Say I have eight replies to the first, it will return 9 entries (one each for the topic + reply, and the individual one with no replies). So, I guess my issue is this: I don't know what to do to limit the number of returns in the final LEFT OUTER JOIN clause to just the most recent, or simply strike the least recent ones out of the window.

(Yes, I realize the ORDER BY... clause is messed up, as it'll first order it by the post create date, then by the comment create date. Yes, I realize I could simplify all my problems by adding two fields into postitInfo, lastCommentCreate and lastCommentCreateID, and have it update each time a reply is made, but... I like the hard way.)

So what am I doing wrong?

Or is this such an inane problem that I should be taken 'round the woodshed and beat with a hammer?

Answer 1

It looks like the last LEFT JOIN is the only one that can return multiple rows. If that's true, you can just use LIMIT 5 to get the last five comments:

 ORDER BY t5.create DESC
 LIMIT 5

If not, a very simple solution would be to retrieve the comments with a separate query:

SELECT *
FROM rantCommentInfo t5
    ON t5.referenceType = 8 
    AND t5.referenceid = t1.id
LEFT OUTER JOIN rantUser t6
    ON t6.id = t5.authorID
ORDER BY CommentCreate
WHERE t5.referenceid = YourT1Id
LIMIT 5

Can't think of a way to do it in one query, without ROW_NUMBER, which MySQL does not support.

Answer 2

The splits between post and postInfo, and the user and userInfo tables, appear to be doing nothing much here except obfuscate things. To better see solutions, let's boil things down to their essence: a table Posts (with a primary key id, a creation date date, and other fields) and a table Comments (with a primary key id, a foreign key refId referencing Posts, a unique creation date date, and other fields); we want to see all posts, each with its most recent comment if any (the primary keys id of the table rows retrieved, and the other fields, can of course be contextually used in the SELECT to fetch and show more info yet, but that doesn't change the core structure, and simplifying things down to the core structure should help illustrate the solutions). I'm assuming the creation date of a comment is unique, otherwise "latest comment" can be ambiguous (of course, that ambiguity could be arbitrarily truncated in other ways, picking one item of the set of "latest comments" to a given post).

So, here's one approach:

SELECT Posts.id, Comments.id FROM Posts
LEFT OUTER JOIN Comments on (Posts.id = Comments.refId)
WHERE Comments.create IS NULL OR (
      Comments.create = (SELECT create FROM Comments
                         WHERE refID = Posts.id
                         ORDER BY create DESC
                         LIMIT 1)
) /* add ORDER BY &c to taste;-) */

the idea: for each post, we want "a null comment" (when there have been no comment to it) or else the comment whose create date is the highest among those referencing the post; here, the inner SELECT takes care of finding that "highest" create date. So, in the same spirit, the inner select might be SELECT MAX(create) FROM Comments WHERE refID = Posts.id which is probably preferable (as shorter and more direct, & maybe faster).