Choosing MySQL in a group with multiple joins

Question

I try to work with the following tables: users (id, name) threads (id, name, slug) messages (id, title, content, topicid, authorid, datetime, slug) responses (id, authorid, threadid, content, datetime)

I want to create a list of posts sorted by most recent reply (or publication date if there are no replies). Each list should contain: title of the post, number of answers, date of publication, date of the last answer, name of the author, name of the person who last answered, etc.

I can get everything except the date and the author of the last answer. Date can be done via MAX (replies.datetime), but I'm not sure how to get the last author.

My last attempt was to use an exception:

select posts.id, posts.title, posts.authorid, posts.topicid, posts.content, posts.datetime, count(replies.id) as replies, r2.datetime, replies.datetime, GREATEST(posts.datetime, coalesce(max(replies.datetime), posts.datetime)) as latesttime, users.name as author, commenters.name as commenter, r2.id from posts left join replies on replies.threadid = posts.id left join users on users.id = posts.authorid left join users commenters on commenters.id = replies.authorid left join replies as r2 on replies.id = r2.id and replies.datetime < r2.datetime where r2.id is null group by posts.id order by latesttime DESC, replies.datetime DESC limit 20; 

Unfortunately, this will not get the last commenter anyway. Any ideas?

Answer 1

You must first start with the innermost request for the maximum identifier for this stream, and its REPLY ID was attached to the original answers to get an ONE instance ... From this, you can get information about the author of the response. THEN, if necessary, get the original wiring information.

 SELECT p.id, p.title, p.content, p.topicid, p.authorid, p.datetime as postdate, p.slug, postUser.Name as PostAuthor, coalesce( MaxReplyUser.NumReplies, 0 ) NumReplies, coalesce( MaxReplyUser.name, '' ) ReplyUser, coalesce( MaxReplyUser.AuthorID, 0 ) ReplyAuthor, coalesce( MaxReplyUser.ThreadID, 0 ) ReplyThread, coalesce( MaxReplyUser.DateTime, '' ) ReplyDateTime, coalesce( MaxReplyUser.Content, '' ) ReplyContent from Posts p left join ( SELECT u1.name, r1.authorid, r1.threadid, r1.datetime, r1.content, MaxReplies.NumReplies from ( SELECT threadid, COUNT(*) as NumReplies, MAX( id ) as MaxReplyID from replies group by threadID ) MaxReplies INNER JOIN replies r1 ON MaxReplies.MaxReplyID = r1.id INNER JOIN Users u1 ON r1.AuthorID = u1.ID ) MaxReplyUser ON p.id = MaxReplyUser.threadID inner join users postUser ON p.authorid = postuser.id order by if( MaxReplyUser.threadID IS NULL, p.DateTime, MaxReplyUser.DateTime ) DESC 

Answer 2

uld will you test it?

 SELECT posts.id, posts.title, posts.authorid, posts.topicid, posts.content, posts.datetime, (SELECT name FROM users WHERE id = posts.autorid) "Author", (SELECT COUNT(*) FROM replies WHERE threadid = posts.id) "Replies", (SELECT MAX(datetime) FROM replies r WHERE threadid = posts.id) "Lastreplytime", (SELECT (SELECT name FROM users WHERE id = r.authorid LIMIT 1) FROM replies r WHERE threadid = posts.id ORDER BY datetime DESC LIMIT 1) FROM posts ORDER BY Lastreplytime DESC LIMIT 20;