0

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

2 Answers 2

2

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

1
  • 1
    So nice, I got it working !!! Thanks a lot.
    – gordie
    May 11, 2020 at 7:32
-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);
}
1
  • the question is to filter a custom query, I think you did misunderstood it.
    – gordie
    May 7, 2020 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.