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

Question

I've looked over 2 or 3 similar looking threads regarding sortable columns and the meta queries involved with making them work. None seem to resolve my exact problem.

I am using the pre_get_posts filter to hook the sortable logic for an admin column. I have a post meta checkbox that I'd like to sort, it's key is _prioritize_s.

When checked, the value is 'yes'. If it has never been checked, then neither the key nor value exists. If it's been checked before, and then unchecked, the key exists, but the value is an empty string.

Here is how I'd like it to order:

• meta key _prioritize_s = 'yes', from the latest to oldest post
• meta key _prioritize_s = empty string OR NOT EXISTS, from the latest to oldest post

Here is what I have. It kind of works, but the date order doesn't seem to work properly and I need to group the NOT EXISTS query with the one where the value could be an empty string?

class Featured_Admin_column {

    public $column_names = [
        'prioritized_post' => '_prioritize_s',
    ];

    function init() {
        $this->hooks();
    }

    function hooks() {
        add_filter( 'manage_edit-post_sortable_columns', [ $this, 'create_sortable_columns' ] );
        add_action( 'pre_get_posts', [ $this, 'set_meta_for_sortable_columns' ] );

    }

    function create_sortable_columns( $columns ) {
        $columns['prioritized_post'] = 'priority';

        return $columns;
    }

    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' );
        }
    }

---

Update: I've tried messing around with this some more, and thinking maybe WP considers blank values as null as well. When I tried putting the meta query on a page template so I could echo out things and test before I mess with the admin columns, I tried reducing the meta query for 2 arrays instead of 3. I get both posts with and without the post meta _prioritize_s but the date order is still out of whack despite using an array in the orderby. What am I missing?

$test_sorting_by_priority = get_posts( [
    'posts_per_page' => - 1,
    'order'          => 'DESC',
    'orderby' => array(
        'exists' => 'date',
        'empty' => 'date',
    ),
    'meta_query'     => array(
        'relation' => 'OR',
        'exists'   => array(
            'key'     => '_prioritize_s',
            'value'   => 'yes',
            'compare' => 'EXISTS',
        ),
        'empty'    => array(
            'key'     => '_prioritize_s',
            'compare' => 'NOT EXISTS'
        ),
    ),
] );

foreach ( $test_sorting_by_priority as $test ) {
    $empty_or_null = isset( $test->{'_prioritize_s'}) ? $test->{'_prioritize_s'} : 'null';

    echo $test->ID . ' ================== <b>' . $test->post_date . ' ===== ' . $empty_or_null . '</b> <br/>';
}

Answer 1

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.