1

I have this structure:

posts:
    id, content, created_at

comments:
    id, post_id, content, created_at

now I would like to select 10 posts ordered by created_at desc, limit to only 10 posts. AND now the problem, I would like to get each post last 5 comments.

I have no problem doing this seperatly, but it will produce me 11 queries.... one fore getting the posts, and another 10 queries for each posts and selecting it's comments.

any ideas on how to reduce this queries?

Tzook Bar Noy
  • 11,337
  • 14
  • 51
  • 82

2 Answers2

3

Here is one way:

select c.*
from (select p.*
      from posts p
      order by createdat desc
      limit 10
     ) p10 left outer oin
     comments c
where 5 >= (select count(*)
            from comments c2
            where c2.post_id = c.post_id and c2.created_at >= c.created_at
           );

This will work best with the indexes posts(createdat) and comments(post_id, created_at).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1
SELECT   p.id,p.content,p.created_at 
FROM posts p 
ORDER BY created_at DESC LIMIT 10
JOIN 
(SELECT id, post_id, content, created_at 
FROM comments 
ORDER BY created_at DESC 
LIMIT 5)x
ON x.post_id=p.id
Mihai
  • 26,325
  • 7
  • 66
  • 81