Sortable admin column for one meta key with three possible meta values

OK, so the problem is pretty clear to explain. These posts are sorted as three different groups:

• ‘yes’
• ”
• null

That’s because empty value (”) is not the same as null (not existing values).

One way to solve it will be to use custom posts_orderby filter. You can use case statement:

order by case when priority = 'yes' then 1 else 0 end, date desc

So it may look like this:

function set_meta_for_sortable_columns( $query ) {
    if ( ! is_admin() ) {
        return;
    }

    $orderby = $query->get( 'orderby' );
    if ( 'priority' == $orderby ) {
        $query->set( 'meta_query', array(
            'relation' => 'OR',
            array(
                'key'   => '_prioritize_s',
                'value' => 'yes',
            ),
            //The 2 arrays below should be grouped together somehow...
            array(
                'key'   => '_prioritize_s',
                'value' => '',
            ),
            array(
                'key'     => '_prioritize_s',
                'compare' => 'NOT EXISTS'
            )
        ) );

        $query->set( 'orderby', 'meta_value date' );
        add_filter( 'posts_orderby', [$this, 'modify_posts_orderby_for_correct_sorting'] );
    }
}

function modify_posts_orderby_for_correct_sorting( $orderby ) {
    remove_filter( 'posts_orderby', [$this, 'modify_posts_orderby_for_correct_sorting'] );  // so this filter is run only during that one query

    global $wpdb;
    $orderby = str_replace( "{$wpdb->postmeta}.meta_value", "case when {$wpdb->postmeta}.meta_value="yes" then 1 else 0 end", $orderby );

    return $orderby;
}

What we do here is:

1. We modify the default query in such way, it will include all values of _prioritize_s meta and sort by that meta value (and later by date). This will result in orderby part of SQL query like so: {$wpdb->postmeta}.meta_value ASC, post.post_date ASC
2. And it’s almost OK. But this will result in bad sorting (because null != ”). That’s why we have to change that orderby clause. So we add our filter function and replace only the first part (the one with meta_value – we leave the date part as it was) of that clause with the clause with CASE statement.
3. And because that filter should modify only that one query, then first thing we do inside the filter function is removing it, so it won’t affect any other queries in that request.

But… much easier way (IMHO) will be to change the way you store values in that meta field. Sorting would be much easier if you’ll store only ‘yes’ value.

So in your code that saves that value, set the meta field only if it’s ‘yes’ and delete that field otherwise.