title: How to sort by derived SQL columns in WordPress?
body: <p>I'm having issues sorting the highest rated posts on my WordPress website.</p>
  
  <p>I'd like to sort and display the highest scored posts (<code>rating_full</code>) that also have the most votes (<code>rating_count</code>).</p>
  
  <p>Both are of type: LongString.</p>
  
  <p>Sort Example:</p>
  
  <p>Business 4 has a rating 4.5/5 - 3 votes
  <br>
  Business 2 has a rating 4.5/5 - 1 votes
  <br>
  Business 1 has a rating 3.5/5 - 2 votes
  <br></p>
  
  <p>In code areas of interest:</p>
  
  <p>The orderby rating is the section I'm concerned about in code.</p>
  
  <pre><code>($orderby == 'rating')
  </code></pre>
  
  <p>The rating score for a post in code is <strong>'rating_full'</strong>. (Scale is 1 to 5)</p>
  
  <p>The vote counts for a post in code is <strong>'rating_count'</strong> (Number of votes)</p>
  
  <p>Both are of type: LongString</p>
  
  <p>Here is an image of the database structure and data:
  <a href="http://s22.postimg.org/wojlwqlz5/database.png" rel="nofollow">http://s22.postimg.org/wojlwqlz5/database.png</a></p>
  
  <p>Here is my code below. I notice my sorting does not always sort the posts in order based on rating_full. I'm not sure why. I have not tried to implement the count DESC as I'm unsure.</p>
  
  <pre><code>// Sorting
  add_filter('posts_join', 'directorySortingJoin',10,2);
  function directorySortingJoin($join, $query) {
  global $wpdb, $aThemeOptions;
      if ($query-&gt;is_main_query() &amp;&amp; !$query-&gt;is_admin &amp;&amp; ((isset($_GET['dir-    search'])) || (isset($query-&gt;query_vars["ait-dir-item-category"])) ||         (isset($query-&gt;query_vars["a-dir-item-location"])))) {
      $sql = "";
      // default ordering
      $orderby = (isset($aThemeOptions-&gt;directory-&gt;defaultOrderby)) ?  $aThemeOptions-&gt;directory-&gt;defaultOrderby : 'post_date';
      // get from get parameters
      if (!empty($_GET['orderby'])) {
          $orderby = $_GET['orderby'];
      }
      if ($orderby == 'rating') {
          $sql .= " LEFT JOIN {$wpdb-&gt;postmeta} rating ON ({$wpdb-&gt;posts}.ID  = rating.post_id AND rating.meta_key IN ('rating_full'))";
      }
      if ($orderby == 'packages') {
          directorySaveUserPackagesToDb();
          $sql .= " LEFT JOIN {$wpdb-&gt;usermeta} packages ON ({$wpdb- &gt;posts}.post_author = packages.user_id AND packages.meta_key IN    ('dir_package'))";
      }
      if (isset($aThemeOptions-&gt;directory-&gt;showFeaturedItemsFirst)) {
          $sql .= " LEFT JOIN {$wpdb-&gt;postmeta} featured ON ({$wpdb-&gt;posts}.ID = featured.post_id AND featured.meta_key IN ('dir_featured'))";
      }
      $join .= $sql;
      }
  return $join;
      }
      add_filter('posts_orderby', 'directorySortingOrderby',10,2);
      function directorySortingOrderby($orderby, $query) {
  global $wpdb, $aThemeOptions;
  if ($query-&gt;is_main_query() &amp;&amp; !$query-&gt;is_admin &amp;&amp; ((isset($_GET['dir-  search'])) || (isset($query-&gt;query_vars["a-dir-item-category"])) ||     (isset($query-&gt;query_vars["a-dir-item-location"])))) {
      $sql = "";
      // default ordering
      $orderby = (isset($aThemeOptions-&gt;directory-&gt;defaultOrderby)) ?     $aThemeOptions-&gt;directory-&gt;defaultOrderby : 'post_date';
      $order = (isset($aThemeOptions-&gt;directory-&gt;defaultOrder)) ? $aThemeOptions-&gt;directory-&gt;defaultOrder : 'DESC';
      // get from get parameters
      if (!empty($_GET['orderby'])) {
          $orderby = $_GET['orderby'];
      }
      if (!empty($_GET['order'])) {
          $order = $_GET['order'];
      }
      if ($orderby == 'rating') {
          if (isset($aThemeOptions-&gt;directory-&gt;showFeaturedItemsFirst)) {
              $sql = "featured.meta_value DESC, convert(rating.meta_value, decimal) {$order}";
          } else {
              $sql = "convert(rating.meta_value, decimal) {$order}";
          }
      } else if ($orderby == 'packages') {
          if (isset($aThemeOptions-&gt;directory-&gt;showFeaturedItemsFirst)) {
              $sql = "featured.meta_value DESC, packages.meta_value   {$order}";
          } else {
              $sql = "packages.meta_value {$order}";
          }
      } else {
          if (isset($aThemeOptions-&gt;directory-&gt;showFeaturedItemsFirst)) {
              $sql = "featured.meta_value DESC, {$wpdb-&gt;posts}.{$orderby}   {$order}";
          }
      }
      $orderby = $sql;
         }
    return $orderby;
        }
  </code></pre>