时间戳

首页 > 解决方案 > 按过去 x 小时内的评论数排序帖子

mysql - 按过去 x 小时内的评论数排序帖子

问题描述

所以我有这段代码，它可以做我想做的事，但它仍然缺少一些东西：

    <?php 
    $hours = 12;

    $result = $wpdb->get_results( $wpdb->prepare( "
    SELECT p.comment_count, p.ID, p.post_title
    FROM $wpdb->posts p
    INNER JOIN $wpdb->comments c ON c.comment_post_ID = p.ID
    WHERE p.post_type = 'post'
    AND p.post_status = 'publish'
    AND ( TIMESTAMPDIFF( HOUR, c.comment_date, NOW() ) <= %d )
    GROUP BY p.ID
    ORDER BY p.comment_count DESC
    LIMIT 0, 6
    ", $hours ) );
        
        
         foreach ($result as $topten) {
             $postid = $topten->ID;
             $title = $topten->post_title;
             $commentcount = $topten->comment_count;
             if ($commentcount != 0) {
      ?>

基本上，我想订购过去 12 小时内评论最多的 6 个帖子。

上面的代码应该做的是：

假设这些帖子在过去 12 小时内评论最多：

Apple (10 comments), 
Orange (8 comments), 
Banana (7 comments), 
Cherry (5 comments), 
Lemon (3 comments), 
Grape (2 comments).

理论上，它们应该按上述顺序排列。

然而，有趣的是。

即使 Grape 有 2 条评论并且应该排在最后，但由于 ALL TIME 它的评论最多，它会排在第一位：

Grape (2 comments), 
Apple (10 comments), 
Orange (8 comments) and etc.

这是不正确的。

基本上，Apple 有 10 条评论都没关系，只要你在 Grape 帖子中写了至少 1 条评论，它就会将 Grape 放在首位。

换句话说，上面的代码不知何故知道哪些水果有最多的评论（所有时间），并将它们按顺序排列，而不管您设置的时间范围以及在此期间收到多少评论。

评论最多的所有其他 ALL TIME 帖子也是如此。

假设所有时间都是这样的：

Grape (300 comments), 
Lemon (200 comments), 
Cherry (200 comments)

使用上面的代码，如果它得到（在过去 12 小时内）：

Cherry (3 comments), 
Lemon (2 comments), 
Grape (1 comment), 
Apple (30 comments), 
Orange (20 comments).

它将像这样订购：

Grape,  
Lemon, 
Cherry, 
all other depending on the ALL TIME.

尽管如此，苹果应该是第一个。

我希望我解释清楚。

我知道我在代码的某个地方犯了一个错误。

我不是编码员。

迫切需要帮助。

标签： mysqlsql

解决方案

您想要过去十二小时内评论最多的六个帖子。为此使用IN（或EXISTS）。

您想按帖子的总评论数对帖子进行排序。您可以通过子查询进行排序。

SELECT p.comment_count, p.id, p.post_title
FROM posts p
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND p.id IN
(
  SELECT c.comment_post_id
  FROM comments c
  WHERE TIMESTAMPDIFF( HOUR, c.comment_date, NOW() ) <= 12
  GROUP BY c.comment_post_id
  ORDER BY COUNT(*) DESC
  LIMIT 6
)
ORDER BY
(
  SELECT COALESCE(COUNT(*), 0)
  FROM comments c
  WHERE c.comment_post_id = p.id
) DESC;

问题在于，这并没有考虑到关系。如果有多个相同计数的帖子，它将任意选择一个帖子作为第六个。为了处理这个替换IN子句：

AND p.id IN
(
  SELECT top6_and_ties.comment_post_id
  FROM comments top6_and_ties
  WHERE TIMESTAMPDIFF( HOUR, top6_and_ties.comment_date, NOW() ) <= 12
  GROUP BY top6_and_ties.comment_post_id
  HAVING COUNT(*) IN
  (
    SELECT COUNT(*)
    FROM comments top6_counts
    WHERE TIMESTAMPDIFF( HOUR, top6_counts.comment_date, NOW() ) <= 12
    GROUP BY top6_counts.comment_post_id
    ORDER BY COUNT(*) DESC
    LIMIT 6
  )
)

在您的 PHP 脚本中，这可能在这里：

$result = $wpdb->get_results( $wpdb->prepare( "
SELECT p.comment_count, p.id, p.post_title
FROM posts p
WHERE p.post_type = 'post'
AND p.post_status = 'publish'
AND p.id IN
(
  SELECT top6_and_ties.comment_post_id
  FROM comments top6_and_ties
  WHERE TIMESTAMPDIFF( HOUR, top6_and_ties.comment_date, NOW() ) <= %d
  GROUP BY top6_and_ties.comment_post_id
  HAVING COUNT(*) IN
  (
    SELECT COUNT(*)
    FROM comments top6_counts
    WHERE TIMESTAMPDIFF( HOUR, top6_counts.comment_date, NOW() ) <= %d
    GROUP BY top6_counts.comment_post_id
    ORDER BY COUNT(*) DESC
    LIMIT 6
  )
)
ORDER BY
(
  SELECT COALESCE(COUNT(*), 0)
  FROM comments c
  WHERE c.comment_post_id = p.id
) DESC
", $hours , $hours ) );

推荐阅读