MySQL Query For Comments & Replies - With LIMIT on both

peppy 0
Viewed 16

I'm trying to build a comments system like facebook, where users can submit comments on posts. At the same time, users will be able to reply to comments. This comments table is going to have millions of rows, so the MySQL query needs to be efficient.

Requirements:

  • I only need 1 level of replies, no huge reply nests, nothing fancy.

  • When the page loads, I only want 10 comments showing (parentid = 0).

  • For each comment, I only want 5 replies to show up (parentid > 0)

  • I will need a count of each, so I can add a "show more" button if there are more comments/replies still left to read.

  • The comments/replies need to be JOINED with my users and profileimages table to bring up usernames and other profile info.

My "comments" table is set up like (id, userid, postid, parentid, comment, timestamp)

My "users" able is set up like (id, username, profileimageid)

My "profileimages" table is set up like (id, path)

So far, I have this mess like:

SELECT gc.id, gc.postid, gc.comment, gc.parentid, gc.userid, users.username, profileimages.path,
GROUP_CONCAT(JSON_OBJECT('id', gc1.id, 'text', gc1.comment, 'userid', gc1.userid, 'username', gc1.username, 'path', gc1.path)) as sub_comments 
FROM comments as gc JOIN users ON gc.userid = users.id JOIN profileimages ON users.profileimageid = profileimages.id 
LEFT JOIN (SELECT gc.id, gc.comment, gc.parentid, gc.userid, users.username, profileimages.path FROM comments as gc JOIN users ON gc.userid = users.id JOIN profileimages ON users.profileimageid = profileimages.id ORDER BY gc.id DESC LIMIT 0,5) as gc1 ON gc.id = gc1.parentid 
WHERE gc.postid = 1 AND gc.parentid = 0 GROUP BY gc.id LIMIT 10

This is something I "Frankenstein'ed" from studying a related topic here and on Google. (The CONCAT/JSON_OBJECT isn't required for me, it was required for that other guy, but I can still use it if it turns out to be useful). The query I have now is big, ugly, inefficient on large tables and I can't sort the replies using "ACS", it's stuck only working on "DESC", and no count figures for the comments and replies either. There's gotta be a better way. I would prefer not having to loop MySQL queries in PHP to get each set of replies for each comment.

Thanks!

mysql php
0 Answers
Related