我需要你的帮助!我需要创建具有多级回复限制的评论系统。这意味着我需要在 parentId 所在的注释上使用 LIMIT。
SELECT gc.id, gc.comment_text, gc.created_at, gc.parent,
GROUP_CONCAT(JSON_OBJECT("id", gc1.id, "comment_text", gc1.comment_text, "created_at", gc1.created_at)) as sub_comments
FROM games_comments as gc
LEFT JOIN (SELECT gc.id, gc.comment_text, gc.parent, gc.created_at FROM games_comments as gc ORDER BY gc.created_at DESC) as gc1 ON gc.id = gc1.parent
WHERE gc.parent = 0
GROUP BY gc.id
ORDER BY gc.created_at DESC
LIMIT 0,5
这只是一种方法,我知道得到回复,但只有 1 个级别,没有任何限制。
桌子:
╔═══╦════════╦══════════╗
║id ║ comment║ parent_id║
╠═══╬════════╬══════════╣
║ 1 ║ text1 ║ 0 ║
║ 2 ║ text2 ║ 0 ║
║ 3 ║ text3 ║ 2 ║
║ 4 ║ text4 ║ 2 ║
║ 5 ║ text5 ║ 2 ║
╚═══╩════════╩══════════╝
现在我需要得到这样的结果
var result = [
{
"id": 1,
"text": "text1",
"replies": [
],
},
{
"id": 2,
"text": "text2",
"replies": [ (with LIMIT 0,2)
{
"id": 3,
"text": "text3"
},
{
"id": 4,
"text": "text4"
}
]
}
]
如您所见,有限制 0,2
谢谢大家的帮助。
您只需要在左连接子查询中设置限制条件。
SELECT gc.id, gc.comment_text, gc.created_at, gc.parent,
GROUP_CONCAT(JSON_OBJECT("id", gc1.id, "comment_text", gc1.comment_text, "created_at", gc1.created_at)) as sub_comments
FROM games_comments as gc
LEFT JOIN (SELECT gc.id, gc.comment_text, gc.parent, gc.created_at FROM games_comments as gc ORDER BY gc.created_at DESC LIMIT 0,5) as gc1 ON gc.id = gc1.parent
WHERE gc.parent = 0
GROUP BY gc.id
ORDER BY gc.created_at DESC
请尝试此查询。
根据您的演示表,我创建了此查询,该查询可根据您的要求正常工作。下面是我测试的示例查询。
SELECT gc.id, gc.comment, gc.parent_id,
GROUP_CONCAT(JSON_OBJECT("id", gc1.id, "comment", gc1.comment)) as sub_comments
FROM test as gc
LEFT JOIN (SELECT gc.id, gc.comment, gc.parent_id FROM test as gc
ORDER BY gc.id DESC LIMIT 0,2) as gc1 ON gc.id = gc1.parent_id
WHERE gc.parent_id = 0
GROUP BY gc.id