MySQL LIMIT 0,15 where 15 is the number of parent

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:

Eg 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.

---

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

---

What about this:

SELECT sc.id,
  sc.deleted,
  sc.comment,
  sc.user_id,
  sc.created,
  sc.parent_id,
  s.id as sid,
  s.user_id submissionUserId,
  u.username,
  u.photo AS userPhoto,
  coalesce(p.id, sc.id) as ocol1,
  coalesce(p.created, sc.created) as ocol2
FROM submissions_comments AS sc
LEFT JOIN users AS u ON sc.user_id = u.id
LEFT JOIN submissions AS s ON sc.submission_id = s.id
LEFT JOIN submissions_comments AS p on p.id = sc.parent_id
where coalesce(p.id, sc.id) between 0 and 15
and sc.deleted = 0
ORDER BY ocol1, ocol2

Obviously, this is using the id column to 'paginate' the rows - if they are frequently deleted, this could leave a page sparse or empty. I may have a stab at altering it to select a given number of parents, not just a range. If you are interested in this approach, feel free to clarify your requirements in this area.

链接地址: http://www.djcxy.com/p/80812.html

上一篇: 你是否应该在成员函数中传递成员变量？

下一篇: MySQL LIMIT 0,15其中15是父数