Sign Up Login Videos
0

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

0

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

@Francis. 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.

[HTML_REMOVED]

[HTML_REMOVED]

<?

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

}


?>

[HTML_REMOVED]

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

[HTML_REMOVED]

[HTML_REMOVED]

<?

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


?>

[HTML_REMOVED]

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.

  • PHP
  • SQL
  • Social Network

0 Replies

x