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!