用于排序注释及其嵌套回复的MySQL查询 [英] MySQL query for sorting comments and their nested replies

问题描述

我已经阅读了10个相关的帖子在这里和其他地方，仍然无法想象这一个，对php和MySQL更新：

I have read over 10 related posts here and elsewhere and still can't figure this one out, being rather new to php and MySQL:

我使用WordPress并尝试在旧主题中实现嵌套注释。我的表是所有设置（我们称之为'评论'），并有以下字段：

I am using WordPress and trying to implement nested comments within an old theme. My table is all set (let's call it 'comments') and has the following fields:

comment_ID | comment_date | comment_parent, etc.

comment_parent 强>在顶级评论;它等于嵌套回复中回复的评论的 comment_ID 。

comment_parent remains equal to 0 in top level comments; it is equal to the comment_ID of the comment replied to in nested replies.

原始的MySQL查询如下所示：

The original MySQL query looks like this:

SELECT * FROM $wpdb->comments 
WHERE comment_post_ID = %d AND comment_approved = '1' 
ORDER BY comment_date ASC LIMIT %d"

通过后面的php并输出注释列表，注释按日期列出不考虑嵌套回复，如下：

Through the php that follows and outputs the comment list, comments are listed by date without respecting nested replies as such:

comment_ID | comment_date | comment_parent
100          Jan 01         0      (this is a top level comment)
104          Jan 03         0      (this is a top level comment)
106          Jan 04         100    (this is a reply to the first comment)
108          Jan 05         104    (this is a reply to the second comment)

显然，由于我按日期排序，在下面的评论100，评论108应该在评论104之下。

Obviously, the sequence is broken since I am sorting by date. Comment 106 should appear right below comment 100, and comment 108 should be below comment 104.

我试图不改变我的PHP，我想这样做与MySQL查询，但可以't得到它的权利。我已经尝试使用JOIN，GROUP BY，HAVING作为类似问题的建议，没有任何成功。

I am trying not to change my php and I would like to do this with the MySQL query but can't get it right. I have tried using JOIN, GROUP BY, HAVING as suggested in similar questions, without any success. Is there a way for me to achieve the correct sorting right from the query and keep my php intact?

推荐答案

好吧，我的方法是，最终决定了它，感谢Nupul的评论上面谁说：你可以先获取所有的顶级评论，然后在第二轮的嵌套评论（所有），并相应地填充你的内容。我确实尝试过这个选项不成功，但这促使我坚持...

Well, I've finally figured it out, thanks partially to Nupul's comment above who said that: "You can first fetch all the top level comments in one go and then the nested comments (all) in the second round and populate your content accordingly.". I had indeed tried that option unsuccessfully, but this motivated me to persevere...

为了记录，希望这可以帮助未来的人， ve固定它。 两个单独的MySQL查询首先提取顶级注释，其次，嵌套注释回复（这些查询的格式为WordPress。）

For the record and hoping that this can help someone in the future, here's how I've fixed it. Two separate MySQL queries fetching first the top level comments and second, the nested comment replies (these queries are formatted for WordPress.)

$comments = $wpdb->get_results($wpdb->prepare("
  SELECT * 
  FROM $wpdb->comments 
  WHERE comment_post_ID = %d AND comment_approved = '1' AND comment_parent = '0' 
  ORDER BY comment_date ASC 
  LIMIT %d
",etc,etc)); 

$replies = $wpdb->get_results($wpdb->prepare("
  SELECT * 
  FROM $wpdb->comments 
  WHERE comment_post_ID = %d AND comment_approved = '1' AND comment_parent <> '0' 
  ORDER BY comment_date ASC 
  LIMIT %d
",etc,etc));

然后我进入我的主要注释循环（FOREACH）寻找 em>。在这个循环中，在底部，我跳到一个嵌套的IF循环，寻找嵌套的注释

Then I go into my main comment loop (FOREACH) looking for top level comments only. Within that loop, at the bottom, I jump into a nested IF loop looking for nested comments where

$ reply-> comment_parent == $ comment-> comment_ID （比较我的2个MySQL查询）

$reply->comment_parent == $comment->comment_ID (comparing my 2 MySQL queries)

，如果真的调整 $ comments 以便我的嵌套评论由WordPress正确回显。然后我将 $ comments 的值返回到其原始值，并退出我的嵌套注释循环回主FOREACH。

and if true adjusting $comments to equal $replies so that my nested comments are echoed correctly by WordPress. I then return the value of $comments to its original and exit my nested comment loop back to the main FOREACH.

在我的推理或执行中可能有缺陷，但它的作用就像一个魅力！当然，这不提供额外的嵌套级别，所有嵌套的注释是一个接一个写的，即使他们是彼此的回复，但是对我来说足够好了！

There are probably flaws in my reasoning or execution but it works like a charm! Of course this does not provide for additional nested levels and all nested comments are written one after the other even if they are replies to each other, but that's good enough for me!

要进行演示，请 http://www.vincentmounier.com/blog2/ ，然后点击任意一个帖子底部的显示评论链接。 Nupul，再次感谢你的动机！

For a demo, http://www.vincentmounier.com/blog2/ and click on any of the "Show Comments" links at the the bottom of posts. Nupul, thanks again for the motivation!

这篇关于用于排序注释及其嵌套回复的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！