Order by optional meta key?

I’m building the sorting functionality for a custom post type, and I have a custom meta value for “Featured” posts. This value is set when you tick the checkbox, otherwise it is not set.

Using the default orderby=meta_value with meta_key=featured makes it so that the screen only displays posts that have the meta key. If they don’t, they don’t even appear.

I want it so if it is not set, they do appear, but they appear last. I assume I need to use meta_query instead, but I couldn’t get that to work either.

How do I allow empty, false, or non-existing meta keys in the WP_Query arguments?

My code is below. This is for sorting columns in the dashboard, so it is modifying the default WP Query args.

function featured_sortable_order( $vars ) {
  if ( isset($vars['orderby']) && $vars['orderby'] == 'featured' ) {

    $vars = array_merge( $vars, array(
      'meta_key' => 'featured',
      'orderby' => 'meta_value',
      'order'     => isset($vars['order']) ? $vars['order'] : 'asc',
    ) );

  }
  return $vars;
}
add_filter( 'request', 'featured_sortable_order' );

Completely edited after first publish

The problem is that to order for a meta value, WordPress need to 'meta_key' in query is set to something.
But if you set 'meta_key' to something, then WordPress will add something like

AND ( wp_postmeta.meta_key = 'the_meta_key' )

to WHERE SQL clause; and something like

INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )

to join clauses. So query returns only the posts that has that meta query.

Once you are working on backend, and you call get_post_meta( $postid, 'featured', true) for every post to show the column, is not a great performance issue call it 2 times thanks to WordPress cache on get meta function.

So, the idea is get all the posts (without adding filter on meta key), then filter posts using 'posts_results' hook and order posts looking at the ‘featured’ meta key.

I’ll remove the filter just after having used it.

add_filter( 'posts_results', 'order_by_featured', PHP_INT_MAX, 2 );

function order_by_featured ( $posts, $query ) {
  // run only on admin, on main query and only if 'orderby' is featured
  if ( is_admin() && $query->is_main_query() && $query->get('orderby') === 'featured' ) {
    // run once
    remove_filter( current_filter(), __FUNCTION__, PHP_INT_MAX, 2 );
    $nonfeatured = array();
    $featured = array();
    foreach ( $posts as $post ) {
      if ( get_post_meta( $post->ID, 'featured', TRUE ) ) {
        $featured[] = $post;
      } else {
        $nonfeatured[] = $post;
      }
    }
    $order = strtoupper( $query->get('order') ) === 'ASC' ? 'DESC' : 'ASC';
    // if order is ASC put featured at top, otherwise put featured at bottm
    $posts = ( $order === 'ASC' )
      ? array_merge( $nonfeatured, $featured )
      : array_merge( $featured, $nonfeatured );
  }
  return $posts;
}

In addition, I add a filter on 'pre_get_post' to use 'ASC' as default order if no order is set in the query:

add_action( 'pre_get_posts', function( $query ) {
  // if no order is set set order to ASC
  if (
    is_admin() && $query->is_main_query()
    && $query->get('orderby') === 'featured' 
    && $query->get('order') === ''
  ) {
    $query->set( 'order', 'ASC' );
  }
});