Column sorting with emtpy meta values

I am trying to sort my custom post type columns in the admin area. There’s two ways I found to do this:

At first this was my way to do it, however I got some strange behaviours. If a meta value is not set, the filter will remove the post from the list, rather than sorting it.

add_filter('request', array($this, 'sort_columns') );
public function sort_columns( $vars ) { 
    if ( isset( $vars['orderby'] ) && 'startdate' == $vars['orderby'] ) {
        $vars = array_merge( $vars, array(
            'meta_key' => '_start_event_datetime',
            'orderby' => 'meta_value_num'
        ) );
    }
}

Hoping to solve this strange behaviour I came across another way to do it, but I got the same behaviour.

add_action('pre_get_posts', array($this, 'sort_columns') );
public function sort_columns( $query ) { 
    $orderby = $query->get( 'orderby');  

    if( 'startdate' == $orderby ) {  
        $query->set('meta_key' , '_start_event_datetime');  
        $query->set('orderby' , 'meta_value_num');  
    }  
}

How do I get my posts that have no meta value set to be sorted instead of removed?

The problem is that by setting the posts to be ordered by _start_event_datetime you’re creating the implicit assumption that the post will have that meta. This means the SQL query WordPress generates will only fetch posts of your custom post type with that meta.

There are two possible solutions to this:

• Replace your current query with a slightly more complex meta query
• Modify the SQL query to use an Outer Join rather than an Inner Join

I’ll show you how to do the first as it’s the most sensible way of doing this. The second option would make sense for more advanced sorting like sorting by multiple meta keys or taxonomies I’d recommend using the posts_clauses hook and looking into this blog post.

Using a meta query:

$query->set('meta_query',array(
    'relation'  => 'OR',
    array(
        'key'       => '_start_event_datetime',
        'compare'   => 'EXISTS'
    ),
    array(
        'key'       => '_start_event_datetime',
        'compare'   => 'NOT EXISTS',
        'value'     => 'bug #23268' // Allows WP-Librarian to run on pre-3.9 WP installs
    )
));
$query->set('orderby',   'meta_value_num');

This query gets all posts with or without the meta key. Note that the value for the NOT EXISTS condition is completely abritrary and is only there because of a bug in pre-3.9 WordPress.