title: Order Table by its Foreign Key's Table Column
body: <p>I have a comments table with a <code>post_id</code> foreign key for posts table. The comments table has <code>likes/dislikes</code> columns. How do I order the <code>posts</code> by the number of <code>likes</code> or <code>dislikes</code> in the <code>comments table DESC</code>?</p>
  <p>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 <code>posts.comments DESC</code> as I don't have a workaround to this</p>
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>&lt;?
  
  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-&gt;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-&gt;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'=&gt;$orderTags));
  
  }
  
  ?&gt;</code></pre>
  </div>
  </div>
  </p>
  <p><strong>UPDATE</strong></p>
  <p>The code is updated to reflect the <code>INNER JOIN</code> I added that fixed my issue</p>