CODEWITHSUNDEEP
mysqlsql
Aron Woost
Aron Woost

Reputation: 20688

How to LIMIT a JOIN?

Ok, hours of SO digging, still I haven found a solution for a - IMO rather obvious - task. I have posts and I want to query up to 5 comments per post (the newest maybe).

So basically something like this:

SELECT p.id, p.title, c.id, c.text
FROM posts p
LEFT JOIN comments c ON p.id = c.postId LIMIT 5

(Pseudo, does not work)

How to LIMIT a JOIN?

Upvotes: 3

Views: 188

Answers (3)

AdamMc331
AdamMc331

Reputation: 16730

This looks like a [greatest-n-per-group] problem. The link is to the other tagged question on this site. I would start by getting all posts/comments as you have, and then you can limit it to the most recent 5 for each post like this:

SELECT p1.*, c1.*
FROM posts p1
LEFT JOIN comments c1 ON c1.post_id = p1.id
WHERE(
   SELECT COUNT(*)
   FROM posts p2
   LEFT JOIN comments c2 ON c2.post_id = p2.id
   WHERE c2.post_id = c1.post_id AND c2.commentDate >= c1.commentDate
) <= 5;

Here is another reference on the topic.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425813

SELECT  *
FROM    posts p
LEFT JOIN
        comments c
ON      c.post_id = p.id
        AND c.id >=
        COALESCE(
                (
                SELECT  ci.id
                FROM    comments ci
                WHERE   ci.post_id = p.id
                ORDER BY
                        ci.post_id DESC, ci.id DESC -- You need both fields here for MySQL to pick the right index
                LIMIT   4, 1
                ), 0
                )

Create an index on comments (post_id) or comments (post_id, id) (if comments is MyISAM) for this to work fast.

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can do it using variables:

SELECT pid, title, cid, text
FROM (
  SELECT p.id AS pid, p.title, c.id AS cid, c.text,
         @row_number:= IF(@pid = p.id,
                          IF (@pid:=p.id, @row_number+1, @row_number+1),
                          IF (@pid:=p.id, 1, 1)) AS rn
  FROM posts p
  CROSS JOIN (SELECT @row_number := 0, @pid := 0) AS vars
  LEFT JOIN comments c ON p.id = c.postId 
  ORDER BY p.id ) t  <-- add comments ordering field here
WHERE t.rn <= 5

Demo here

Upvotes: 0

Related Questions