php - 按外键的表列对表进行排序

Question

我有一个评论表，其中包含 post_id帖子表的外键。评论表有likes/dislikes列。如何订购posts按数量likes或dislikes在 comments table DESC ？

评论和帖子是分开加载的。评论 API 运行良好，并相应地对评论进行排序(最喜欢/最不喜欢)，但是在显示帖子时，我按 posts.comments DESC 对它们进行排序。因为我没有解决方法

<?

switch ($orderReactions) {
  case "pdt":
    if ($orderTags == "alltags") {

      $orderBy = "WHERE users.id = posts.user_id ORDER BY posts.posted_at";
    }else {
      $orderBy = "WHERE users.id = posts.user_id AND tags=:tag ORDER BY posts.posted_at";
    }
    
    break;
  case "mlp":
    if ($orderTags == "alltags") {

      $orderBy = "WHERE users.id = posts.user_id AND posts.likes != 0 ORDER BY posts.likes";
    }else {
      $orderBy = "WHERE users.id = posts.user_id AND tags=:tag AND posts.likes != 0 ORDER BY posts.likes";
    }
    
    break;
  case "mdp":
    if ($orderTags == "alltags") {

      $orderBy = "WHERE users.id = posts.user_id AND posts.dislikes != 0 ORDER BY posts.dislikes";
    }else {
      $orderBy = "WHERE users.id = posts.user_id AND tags=:tag AND posts.dislikes != 0 ORDER BY posts.dislikes";
    }
    
    break;
  case "mcp":
    if ($orderTags == "alltags") {

      $orderBy = "WHERE users.id = posts.user_id AND posts.comments != 0 ORDER BY posts.comments";
    }else {
      $orderBy = "WHERE users.id = posts.user_id AND tags=:tag AND posts.comments != 0 ORDER BY posts.comments";
    }
    
    break;
  case "mlc":
    if ($orderTags == "alltags") {

      $orderBy = "INNER JOIN comments ON comments.post_id = posts.id WHERE posts.user_id = users.id AND posts.comments != 0 AND comments.likes != 0 GROUP BY post_id ORDER BY comments.likes";
    }else {
      $orderBy = "INNER JOIN comments ON comments.post_id = posts.id WHERE posts.user_id = users.id AND tags=:tag AND posts.comments != 0 AND comments.likes != 0 GROUP BY post_id ORDER BY comments.likes";
    }
    
    break;
  case "mdc":
    if ($orderTags == "alltags") {

      $orderBy = "INNER JOIN comments ON comments.post_id = posts.id WHERE posts.user_id = users.id AND posts.comments != 0 AND comments.dislikes != 0 GROUP BY post_id ORDER BY comments.dislikes";
    }else {
      $orderBy = "INNER JOIN comments ON comments.post_id = posts.id WHERE posts.user_id = users.id AND tags=:tag AND posts.comments != 0 AND comments.dislikes != 0 GROUP BY post_id ORDER BY comments.dislikes";
    }
    
    break;
  default:
    if ($orderTags == "alltags") {

      $orderBy = "WHERE users.id = posts.user_id ORDER BY posts.posted_at";
    }else {
      $orderBy = "WHERE users.id = posts.user_id AND tags=:tag ORDER BY posts.posted_at";
    }
    break;
}

//posts from users followed by current logged in user + logged in user
if ($orderTags == "alltags") {

  $followingposts = $db->query('SELECT posts.id, posts.body, posts.posted_at, posts.likes, posts.dislikes, posts.tags, posts.user_id, users.`username`, users.`profileimg` FROM users, posts
    '.$orderBy.' DESC LIMIT 10 OFFSET '.$start.';');
  
}else {

  $followingposts = $db->query('SELECT posts.id, posts.body, posts.posted_at, posts.likes, posts.dislikes, posts.tags, posts.user_id, users.`username`, users.`profileimg` FROM users, posts
    '.$orderBy.' DESC LIMIT 10 OFFSET '.$start.';', array(':tag'=>$orderTags));

}

?>

更新

代码已更新以反射(reflect)INNER JOIN我补充说解决了我的问题

Answer 1

我假设您正在编写一个类似 Stack Overflow 的网站，其中您有多个评论(答案)，并且每个评论都可以有喜欢和不喜欢(赞成票和反对票)。

我认为您能做的最好的事情就是将所有这些内容组合成一个存储过程。从那里您可以传入一个参数来确定顺序。就您遇到的实际问题而言，您可以从评论表中对帖子表进行内部联接，然后您就可以简单地按comments.likes和comments.dislikes排序。

以下内容是 mssql 服务器的正确语法，但我确信可以轻松转换为 mysql。

CREATE PROCEDURE spGetPostsByNumberOfComments @OrderByType VARCHAR(50)
AS
 BEGIN
     SELECT *
     FROM Posts
          INNER JOIN Comments ON Comments.PostId = Posts.PostId
    ORDER BY
        CASE WHEN @OrderByType = 'CommentLikes' THEN Comments.Likes
        WHEN @OrderByType = 'CommentDislikes' THEN Comments.dislikes 
        WHEN @OrderByType= 'PostLikes' THEN Posts.likes
        WHEN @OrderByType= 'PostDislikes' THEN Posts.dislikes
        ELSE Posts.PostId
        END DESC
 END;