MySQL Query, Join and Myself, or how I always try
I am creating a small forum.
Attempting to run a request SElECT... JOIN...
is taking too much information about individual messages, as well as the last response (if any). As part of my desire to make things harder, this spans five tables (only the columns referenced by this question are 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...
to get the latest topics and their latest 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 topic messages. Let's say I have two of them and they both look great. Suppose I have eight responses to the first, it will return 9 records (one each for the topic + the answer, and the individual one for no replies). So I think my problem is this: I don't know what to do to limit the number of returns in the last declaration LEFT OUTER JOIN
to the very last one, or just hit the last ones from the window.
(Yes, I understand the suggestion is ORDER BY...
confused as it will first order it by the date the post was created and then by the date the comment was created. Yes, I understand I can simplify all my problems by adding two fields to postitInfo, lastCommentCreate and lastCommentCreateID. and update them every time a response is generated, but ... I really like it.)
So what am I doing wrong?
Or is it such a stupid problem that I should be taken "around a wooded area and beaten with a hammer?"
a source to share
Sharing between post
and postInfo
and tables user
and userInfo
doesn't seem to do anything here other than obfuscating things. To see the solutions better, let's work to the bottom: a table Posts
(with a primary key id
, date of creation date
and other fields) and a table Comments
(with a primary key id
, foreign key refId
referencing Posts
, unique date of creation date
and other fields); we want to see all posts, each with its last comment, if any (the primary keys of the id
extracted table rows and other fields can of course be contextually used inSELECT
to extract and display additional information, but this does not change the basic structure, and simplifying to the basic structure should help illustrate the solutions). I assume that the date the comment was created is unique, otherwise the "last comment" may be ambiguous (of course, this ambiguity can be arbitrarily truncated in other ways, choosing one member of the "last comment" set for 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;-) */
Idea: for each post, we want a "zero comment" (when there were no comments), or the comment with the highest creation date among those who link to the post; here the inner one SELECT
takes care of finding this "highest" date of creation. So in a similar vein, the inner choice may be SELECT MAX(create) FROM Comments WHERE refID = Posts.id
, which is probably preferable (shorter and more direct and possibly faster).
a source to share
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 is to get the comments with a separate request:
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
There is no way I can think of a way to do this in a single query without ROW_NUMBER, which MySQL does not support.
a source to share