Sign Up Login Videos
0

Order Table by its Foreign Key's Table Column

0

Order Table by its Foreign Key's Table Column

I have a comments table with a post_id foreign key for posts table. The comments table has likes/dislikes columns. How do I order the posts by the number of likes or dislikes in the comments table DESC?

The comments and posts are loaded separately. The comments API works well and orders the comments accordingly (most liked/disliked), however when displaying the posts, I order them by posts.comments DESC as I don't have a workaround to this

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 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
    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, posts.user_id, 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));

}
  • PHP
  • SQL
  • Social Network

1 Replies

0

Added an INNER JOIN

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

}
≪ Show more ≫
0

Added an INNER JOIN

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

}
≪ Show more ≫
x