Limit query for each group

Question

I have this query:

select pl.photo_id, pl.user_id, pl.liker_id, p1.filename user_filename, p2.filename liker_filename 
FROM photo_likes pl 
 left join photos p1 on (pl.photo_id = p1.photo_id) 
 left join photos p2 on (pl.liker_id = p2.user_id and p2.avatar = 1)
where pl.user_id = $id order by pl.liker_id, pl.date_liked desc

It gets the correct data, but I would like to modify it to limit the data. So, in a nut shell, this query will get all the likes from all the people that liked a photo of theirs, it works great, this can grab lots of photos for each person. But I want to limit it to get only 5 from each person:

So, say user A likes 10 of my photos, user B likes 8 of my photos, and user C likes 2 of my photos, I only want the last 5 from user A, the last 5 from user B and the last 2 from user C. If that makes sense, how can this be done?

Answer 1

The query you have is good, but I'm wrapping that and using MySQL variables to check each return variable and increase the sequence per each "liker". When the liker changes, set the sequence back to 1.... Then, apply HAVING < 6 for the sequence. You can't do it in the WHERE clause because you want EVERY record to be QUALIFIED which keeps updating the @likeSeq and @lastLiker. Only AFTER that is done, the HAVING says... AFTER that, if the seq is greater than you 5 cap, it throws it out.

per alternate rows being included per your print-screens...

select
      AllRanks.*
   from 
      ( select
                PreQualified.*,
                @likeSeq := if( PreQualified.Liker_ID = @lastLiker, @likeSeq +1, 1 ) as Seq,
                @lastLiker := PreQualified.Liker_ID
             from
                ( select 
                        pl.photo_id, 
                        pl.user_id, 
                        pl.liker_id, 
                        p1.filename user_filename, 
                        p2.filename liker_filename 
                     FROM 
                        photo_likes pl 
                           left join photos p1 
                              on pl.photo_id = p1.photo_id
                           left join photos p2 
                              on pl.liker_id = p2.user_id 
                             and p2.avatar = 1
                     where 
                        pl.user_id = $id 
                     order by 
                        pl.liker_id, 
                        pl.date_liked desc ) PreQualified,
                ( select @lastLiker := 0,
                         @likeSeq := 0 ) sqlvars
      ) AllRanks
   where
      AllRanks.Seq < 6

Answer 2

Could you wrap your joined tables in a sub query?

select ...
from photo_likes
left join photos p1 ...
left join (select p2.filename liker_filename from photos where p1.liker_id = p2.user_id and avatar = 1 LIMIT 5) p2
where ...