3

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/>';
}
  • Could you show the code that you use for registering the column and how you assign set_meta_for_sortable_columns function? – Krzysiek Dróżdż Jan 28 at 16:57
  • @KrzysiekDróżdż I've edited my question above. I only added enough so you could see the hooks and the function call - there is more in this class in real life, but didn't think it relevant. Let me know if you need anything in there. – RachieVee Jan 28 at 18:01
  • It should be enough - I'll take a look at this later today. – Krzysiek Dróżdż Jan 28 at 18:07
1
+50

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.

  • That makes sense - to delete the value of the post meta if it's not saved so I only need to check if it exists or doesn't exists. It simplifies things. I'll give your first solution a try, just to ensure it works, and then I'll try your suggestion before I accept the answer. Thanks! – RachieVee Jan 29 at 15:53
  • No problem. I’ve tested the first solution - it’s to much code to write it in here - so if any problems occur, just let me know - I’ll be able to debug it. – Krzysiek Dróżdż Jan 29 at 16:04
  • To make sure I understand your first solution, you're removing the posts_orderby hook that is normally responsible for setting the order by parameter, so we can write a custom sorting - is that a database query? So it only sorts by meta value if the meta value is equal to yes, correct? Then I imagine the rest, just sort normally by date? If you can include this explanation in your answer, I'd appreciate it. Also why set the orderby as meta_value date if we overwrite it? Thanks. – RachieVee Jan 29 at 16:10
  • @RachieVee I've added sample queries and some explanation to my answer. I hope it will make it clearer. – Krzysiek Dróżdż Jan 29 at 16:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.