CODEWITHSUNDEEP
mysqlgreatest-n-per-group
Manolis
Manolis

Reputation: 19

Select latest posts with their n latest comments

This is a common greatest-n-per-group question, but with an extra problem. What i want is to find the latest 20 posts of a user, and for each post, load its latest 5 (or n) comments. Moreover, for pagination needs, i need to know how many other comments each post has. Is it possible to find it out with the same query? (Or at least with the less efficient queries needed)

What i've done now is finding the latest posts and their comments(all):

Finding latest posts of user:

SELECT up.primkey
     , up.sender
     , up.comment
     , up.date
     , up.admin_approved
     , u.username
     , u.avatar
  FROM users_posts up
  JOIN users u
    ON up.sender=u.userkey
 WHERE up.sender=?
   AND up.admin_approved=0 
 ORDER 
    BY primkey DESC LIMIT $from,$to;

Then,i store each primkey in an array, in order to retrieve the comments of these posts.

Finding the comments of the posts:

SELECT c.primkey,c.post_id, c.sender, c.comment, c.date, u.username, u.avatar
FROM users_posts_comments c
LEFT JOIN users u ON c.sender=u.userkey
WHERE c.post_id IN('.implode(",", $posts_array).') AND c.`admin_approved`=0 
ORDER BY c.primkey DESC;

After that, i store each comment to a new array ($comments[$post_id][]=comment info) and then i output the result.

What i want is to modify the second query and limit the comments to the 5 recent, and also to find somehow how many are the total comments of each post in order to show the pagination.

Here's a fiddle... sqlfiddle.com/#!2/e92a6/1

Expected result:

post8

post7

comment-7

comment-6

comment-5

{pages}

post6

...

I know it would be difficult, so what would you recommend me to do(the most efficient way)?

Thanks.

Upvotes: 0

Views: 759

Answers (1)

Strawberry
Strawberry

Reputation: 33945

A faster solution uses variables - but I'm old school...

This query gives you the latest 5 posts for each sender...

 SELECT x.* ,COUNT(*)
   FROM users_posts x 
   JOIN users_posts y 
     ON y.sender = x.sender 
    AND y.date >= x.date 
  GROUP 
     BY x.primkey 
 HAVING COUNT(*) <= 5

So now you can extend that idea to return the 3 most recent comments (if any) for each of those last 5 posts (for each sender)

SELECT a.*, upc.*
   FROM 
      ( SELECT x.* 
          FROM users_posts x 
          JOIN users_posts y 
            ON y.sender = x.sender 
           AND y.date >= x.date 
         GROUP 
            BY x.primkey 
        HAVING COUNT(*) <= 5
      ) a
   LEFT
   JOIN users_posts_comments upc
     ON upc.post_id = a.primkey
   LEFT
   JOIN users_posts_comments z
     ON z.post_id = upc.post_id
    AND z.date >= upc.date
  GROUP
     BY a.sender 
      , a.primkey
      , upc.primkey 
 HAVING COUNT(upc.post_id) <= 3;

Upvotes: 1

Related Questions