order posts by a secondary query that counts items

Question

i'm working on a music plugin.

It uses a custom table tracklist_items to store every tracks of a playlist; which has a structure like this :

|id| |track_id| |tracklist_id| |order| |time|

So if I want to count the total tracks of a tracklist, I have this function :

function get_subtracks_count(){
  global $wpdb;
  if (!$this->post_id) return false;
  $querystr = $wpdb->prepare( "SELECT COUNT(*) FROM `tracklist_items` WHERE tracklist_id = %d", $this->post_id );
  return $wpdb->get_var($querystr);
}

But now, I would like to be able to sort the tracklists by tracks count when doing a query.

How should I achieve that ?

+: I see that people usually sort that kind of stuff using meta values. This query above is fairly simple so I guess it fast. But shouldl I consider rather storing a meta value attached to the tracklist every time I add or remove a track to a tracklist (a process that seems more complex to me thus that I would like to avoid)

Thanks

Answer 1

The way to go is apply custom filters to the WP_Query you are running.

add_filter('posts_clauses', function ($clauses, $query) {
    global $wpdb;

    // only run the filter for a given query
    // do your checks and return early
    if (!$query->is_main_query() || !$query->is_post_type_archive('tracklist')) {
        return $clauses;
    }

    $clauses['fields'] .= ", COUNT(items.id) AS track_count";
    $clauses['join'] .= " LEFT JOIN tracklist_items AS items ON {$wpdb->posts}.ID = items.tracklist_id";
    $clauses['groupby'] = "{$wpdb->posts}.ID";
    $clauses['orderby'] = "track_count DESC";

    return $clauses;
}, 2, 10);

See also https://stackoverflow.com/questions/20119901/counting-number-of-joined-rows-in-left-join

Answer 2

Try this

function get_subtracks_count(){
  global $wpdb;
  if (!$this->post_id) return false;
  $querystr = $wpdb->prepare( "SELECT COUNT(*) FROM `tracklist_items` WHERE tracklist_id = %d", $this->post_id ORDER BY count(*) DESC);
  return $wpdb->get_var($querystr);
}