MySQL LIMIT 0,15 where 15 is the number of parent_ids, not children

Question

I've got a table and I'm trying to fetch comments for ajax pagination.

You can see a SQLFiddle here:

http://sqlfiddle.com/#!2/5d36a/1

But basically, without the LIMIT of 0,15, it works as intended:

E.g. All comments that are children will be displayed with/under the parent comments.

The problem comes when I introduce the LIMIT. In the example above, it'll fetch 15 comments, but because the 16th comment is a child of comment with id=6, it won't be returned. If you change it to LIMIT 0,16, it'll be returned properly.

Basically I want to return ONLY 0,15 parent comments at a time, but unlimited child comments.

I was told to try a temporary table, but then someone else told me that it could be much simpler (and using only one join with an inner select), but I'm just a little confused.

Answer 1

You could make it work using the following query :

SELECT 
  comment.id,
  comment.deleted,
  comment.comment,
  comment.user_id,
  comment.created,
  comment.parent_id,
  s.id as sid,
  s.user_id submissionUserId,
  u.username,
  u.photo AS userPhoto
FROM (SELECT c.* 
      FROM submissions_comments AS c
      WHERE c.parent_id IS NULL
      ORDER BY c.created DESC
      LIMIT 0, 15) AS base
LEFT JOIN submissions_comments AS comment ON (
  comment.parent_id = base.id 
  OR comment.id = base.id
)
LEFT JOIN users u ON comment.user_id = u.id
LEFT JOIN submissions s ON comment.submission_id = s.id
WHERE base.deleted = 0
ORDER BY base.created DESC

You can see it in action : http://sqlfiddle.com/#!2/6d90f5/1

But I don't know if it will be efficient on the performance side.

What it does basically is :

• Grab the 15 first parent comments in a sub query (or inner query)
• For each of those grab the children and a copy of the parent