How to limit participation?
Ok, hours of SO digging, I still found a solution for a pretty obvious problem IMO. I have posts
, and I want to request up to 5 comments
per post (possibly the newest).
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, doesn't work)
How do I limit the connection?
source to share
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
- MyISAM) to make this work quickly.
source to share
This looks like a [largest-per-group] problem . Link to another tagged question on this site. I would start by getting all posts / comments like yours and then you can limit it to the last 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's another link on this topic.
source to share
You can do it with 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
source to share