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
Related Posts:
- When to use WP_query(), query_posts() and pre_get_posts
- Should I use Pre Get Posts or WP_Query
- Using pre_get_posts with WP_Query
- Return only Count from a wp_query request?
- WP_Query orderby post__in remains ineffective in the Loop [closed]
- What is “main query”? [duplicate]
- How to add taxonomy filter on the query fly?
- Changing the meta_query of the main query based on custom query_vars and using pre_get_posts
- How to uniquely identify queries?
- Order Search Results Page by meta_value If no Value Return Remaining Results
- Count the number of images uploded on the website
- pre_get_posts with get_posts
- Changing Posts Per Page and offset with pre_get_posts
- Slow SQL_CALC_FOUND_ROWS Query
- “pre_get_posts” firing on every query
- RSS feed with specific keyword
- Using is_main_query to select custom post type on certain page
- Multiple orderby parameters in pre_get_posts() action
- SQL Statement generated by WP_Query not producing expected results
- Sorting Posts by custom field
- How to use filter hook posts_join for querying taxonomy terms in posts_where?
- How to pass custom parameter to WP_Query for filtering in pre_get_posts
- Query with pre_get_posts to get pagination
- Recommended way to drop a pending query (in pre_get_posts)?
- How to search for posts IN OR title OR content OR author?
- Single page theme that uses pages for the content
- WordPress custom search form with pre_get_posts not work
- If orderby parameter using pre_get_posts is the same for multiple posts what fallback does the query use?
- Can not switch the queried post in pre_get_posts hook
- order by multiple meta keys in pre_get_posts
- WP_Tax_Query with post_tag not working
- Using pre_get_posts on a specific core/query block
- Count total number of post in foreach loop
- Searching through different categories on different pages code is not working
- How to display future posts – modified query still yields 404
- pre_get_posts filter meta_query without conflicting existing meta_query
- When querying a combination of posts and other meta fields, is there a better solution than directly modifying the WHERE value?
- How to change posts order on Category and Tag archives?
- Formulate a url to show posts with both taxonomy terms
- My Main Query Modification is Messing up my dynamic main – why?
- Offset Page Loops and Pagination
- pre_get_posts order by not working
- Adding a parameter to the default query
- Using WP_Query in “parse_query” or “pre_get_post” in Posts2Posts
- Single meta_query query using OR instead of AND in request’s WHERE statement
- How to choose between hooking into per_get_posts or into parse_query
- How to display upcoming events by dat with Modern Events Calendar Lite
- How to change main query based on post meta
- new WP_Query to get max price meta value not working
- Display posts from only one post form in custom query and exclude in main query
- WP_Query, pre_get_posts and offset
- Complex WP SQL Query
- Getting the current post number / make posts increment
- Show scheduled posts in main loop but not in WP_Query?
- How to use wp_query to post count by search term > group post count by date > convert to json. (ex. result 12-21-2020 – 343)
- WP_Query ignoring tax_query when is_singular
- Applying posts_clauses filter to specific queries only
- Filtering out child category posts from parent category archive not working
- Order ascending is ignored in meta query?
- Shouldn’t I be able to modify the main query by this filter?
- How Can I Change The Tax Query For The Main Loop For Taxonomy Archives?
- using pre_get_posts for search results not found
- Hide elements outside loop based on query
- Woocommerce – exclude only older out-of-stock items
- WP Query – order posts by meta field first and then order the rest
- How I can change the condition or compare operator for WP_Query in pre_get_posts
- WP_Query – How to get all posts of specific days of week by custom field date?
- pre_get_posts – editing query, tax_query
- 2 queries with counters
- Having a variable based on $post_count increase when i move to the next page of results
- tax_query in pre_get_posts
- $wp_query->found_posts not returning correct value
- Elementor posts custom query based on tag [closed]
- Exclude post with taxonomy and from the terms (taxonomy & tag) posts count
- `offset` WP_Query argument dont work via `pre_get_posts`
- Replace wp_query with wp_user_query
- Make a products only viewable to a specific user ID in meta_query pre_get_posts
- pre_get_posts pagination not working
- meta_query is overriding default search
- How can I re-query post_type and rewrite the url?
- Woocomerce – Order products by float attribute in archive pages
- Order posts by title and custom field value?
- Limit default Search query to post_title
- why doesn’t this pre_get_posts code work?
- pre_get_posts with WP_Query to prevent posts from specific tags
- Pull posts from all categories if quantity is not met?
- counting post from a wp-query using sticky_post
- Using the same WP_Query for shop and widgets in WooCommerce shop
- Modify query to exclude certain ids and certain parents and its corresponding children
- WP_Query to show post from a category OR custom field – Not 100% answered
- Display Count of posts
- Adding a Theme Customizer Control for Posts_Per_Page
- Order posts by custom column using pre_get_posts
- Sort posts in dashboard using custom field; also include posts where field isn’t set
- Looking for a way to exclude frontpage and nav menu from query filter
- pre_get_posts redirecting
- Custom post types loop on a page template
- Pre_get_post on CPT archive page
- Search query alteration not working for meta values
- How to put posts with some taxonomy on top of others in `pre_get_posts`