select posts with comments with highest likes/dislikes order by xxx desc

Question

I have two tables, posts & comments. The posts table columns are: id, body, user_id, likes, dislikes, time. The comments table columns are: id, body, post_id, user_id, likes, dislikes, time.

Lets take a scenario with two posts (A and B). Post-A has 1 comment with 10-likes & 2-dislikes, Post-B has 1 comment with 5-likes & 12-dislikes.

When ordering the posts via API endpoint category how do I implement an ORDER BY comment.likes/dislikes DESC statement that starts with Post-B if the request is for the most disliked comment. Or start with Post-A if the request is for most liked comment.

This is what my current query, which orders by the number of post comments for any comment request, looks like. Notice I'm not selecting the from the comments table as the comments are loaded for each post ID after the posts are fetched.

<?

if (isset($_GET['ordertags'])) {
  //post tags e.g general/work/school etc
  $orderTags = $_GET['ordertags'];
}else {
  $orderTags = "alltags";
}

if (isset($_GET['orderreactions'])) {
  //post reactions, e.g date time/ most post-likes/dislikes/comments & comment-likes/dislikes etc
  $orderReactions = $_GET['orderreactions'];
}else {
  $orderReactions = "pdt";
}

//declare vars
$orderBy = "";
//get start offset to load the first 10 results
$start = (int)$_GET['start'];

switch ($orderReactions) {
  case "pdt":
    $orderBy = "ORDER BY posts.posted_at";
    break;
  case "mlp":
    $orderBy = "AND posts.likes != 0 ORDER BY posts.likes";
    break;
  case "mdp":
    $orderBy = "AND posts.dislikes != 0 ORDER BY posts.dislikes";
    break;
  case "mcp":
    $orderBy = "AND posts.comments != 0 ORDER BY posts.comments";
    break;
  case "mlc":
    $orderBy = "AND posts.comments != 0 ORDER BY posts.comments";
    break;
  case "mdc":
    $orderBy = "AND posts.comments != 0 ORDER BY posts.comments";
    break;
  default:
    $orderBy = "ORDER BY posts.posted_at";
    break;
}

//posts from users 
if ($orderTags == "alltags") {

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

}else {

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

}


?>

This is what my comments endpoint, which retrieves the most liked/disliked comments without a poblem, looks like.

<?

if (isset($_GET['action'])) {
  //comment action request i.e most liked/disliked/regular i.e postDate
  $action = $_GET['action'];
}else {
  $action = "reg";
}

//declare vars
$orderBy = "";

switch ($action) {
    case "mdc":
        $orderBy = "ORDER BY comments.dislikes DESC";
        break;
    case "mlc":
        $orderBy = "ORDER BY comments.likes DESC";
        break;
    case "reg":
        $orderBy = "ORDER BY comments.posted_at ASC";
        break;
    default:
        $orderBy = "ORDER BY comments.posted_at ASC";
        break;
}

//fetch comments from db
$comments = $db->query('SELECT comments.id, comments.comment, comments.post_id, comments.posted_at, comments.likes, comments.dislikes, users.username, users.profileimg FROM comments, users WHERE comments.post_id = :postid AND comments.user_id = users.id '.$orderBy.';', array(':postid'=>$_GET['postid']));   


?>

So the issue is how do I select the posts with the highest liked/disliked comments in the ORDER BY DESC statement while still excluding the posts with comments with 0 likes/dislikes. Thanks.

Answer 1

Added an INNER JOIN everything is working fine

<?

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));

}
?>

Answer 2

You can use JOINS for excluding the posts with comments 0 likes/dislikes (SELECT Posts.* FROM Posts JOIN Comments ON Posts.id = Comments.post_id WHERE Comments.likes > 0 AND Comments.dislikes > 0)