I dug around quite a bit for a solution to this today, and eventually I found one, even though it’s a bit ugly.
The problem here is in setting up a WordPress query that sorts posts based on a meta value. I wanted to sort a list of pages by template, but I wanted to include all of the pages, even ones that don’t explicitly have a template set. But the default query was only showing the ones that did have the template value.
Several dead ends almost led me to give up, until I realized it was a JOIN issue. Specifically, the need to change an INNER JOIN to a LEFT JOIN. I just needed to figure out how to do that in the context of WP_Query.
Cut to the chase, here’s what I ended up with.
add_action('pre_get_posts', function($query) {
if (!is_admin()) { return; }
$orderby = $query->get('orderby');
if ('_wp_page_template' == $orderby) {
$query->set('meta_key','_wp_page_template');
$query->set('orderby','meta_value');
// Workaround to include items without this meta key
// Based on: https://core.trac.wordpress.org/ticket/19653#comment:11
add_filter('get_meta_sql', function($clauses) {
$clauses['join'] = str_replace('INNER JOIN','LEFT JOIN',$clauses['join']) . $clauses['where'];
$clauses['where'] = '';
return $clauses;
});
}
});
I don’t like doing a str_replace() on part of the pre-built query, but sometimes you gotta do what you gotta do. Also note that this is part of a larger function I am writing that is only for use in the admin side; you could remove that is_admin() check if you want this to work everywhere.
I haven’t had a chance to dig into the details of the query to figure out why the original source post included moving $clauses['where'] into $clauses['join'], but it’s essential. I tried skipping it, and it didn’t work.
Sorry I can’t provide any more context here… but I hope it’s helpful to anyone who finds themselves in a similar situation!
