Order by nested named meta queries with ‘exists’ and ‘not exists’

Note that the key in your orderby array needs to reference an array in meta_query which contains a direct key item, e.g. 'key' => 'tdlrm_mp'. So for example, you should have used 'tdlrm_mp_exists' => 'ASC', and not 'tdlrm_mp_key' => 'ASC' in your $args['orderby'].

However, that wouldn’t actually sort the posts in the way you wanted it to, but I was simply showing you the correct syntax that one should use.

But don’t fret, what you’re trying to do is possible πŸ™‚

And here’s how:

  1. Set the meta_query to:

    $args['meta_query'] = array(
        array(
            'relation'     => 'OR',
            // select posts that have the tdlrm_mp meta
            'has_tdlrm_mp' => array(
                'key'  => 'tdlrm_mp',
                'type' => 'NUMERIC',
            ),
            // select posts that don't have the meta
            'no_tdlrm_mp'  => array(
                'key'     => 'tdlrm_mp',
                'compare' => 'NOT EXISTS',
            ),
        ),
        array(
            'relation'              => 'OR',
            // select posts that have the 1C_quantity_total meta
            'has_1C_quantity_total' => array(
                'key'  => '1C_quantity_total',
                'type' => 'NUMERIC',
            ),
            // select posts that don't have the meta
            'no_1C_quantity_total'  => array(
                'key'     => '1C_quantity_total',
                'compare' => 'NOT EXISTS',
            ),
        ),
    );
    
  2. Set the orderby to none because we’ll use a completely custom one.

  3. Before you run new WP_Query(), use the posts_orderby filter to modify the ORDER BY clause and basically we use the CASE operator to achieve the (pretty complex) sorting you wanted:

    $_filter = true; // enable the filter below
    add_filter( 'posts_orderby', function ( $orderby, $query ) use ( &$_filter ) {
        if ( $_filter ) {
            global $wpdb;
    
            $meta_clauses          = $query->meta_query->get_clauses();
            $has_tdlrm_mp          = $meta_clauses['has_tdlrm_mp']['alias'];
            $has_1C_quantity_total = $meta_clauses['has_1C_quantity_total']['alias'];
    
            // 1st, sort by the meta key, and posts without the meta are placed at
            // the bottom (or 3rd position).
            $orderby = "
    CASE {$has_tdlrm_mp}.meta_key
        WHEN 'tdlrm_mp'          THEN 1
        WHEN '1C_quantity_total' THEN 2
        ELSE 3
    END ASC";
    
            // 2nd, sort by the meta value, only for the posts in position 1 and 2
            // above.
            $orderby .= ",
    CASE {$has_tdlrm_mp}.meta_key
        WHEN 'tdlrm_mp'          THEN {$has_tdlrm_mp}.meta_value+0
    END ASC,
    CASE {$has_1C_quantity_total}.meta_key
        WHEN '1C_quantity_total' THEN {$has_1C_quantity_total}.meta_value+0
    END DESC";
    
            // 3rd, now optionally sort the posts in the 3rd position above. Here,
            // we sort them by the post date.
            $orderby .= ", {$wpdb->posts}.post_date DESC";
        }
    
        return $orderby;
    }, 10, 2 );
    
    $query = new WP_Query( $args );
    $_filter = false; // disable the filter above
    

Update

Sorry, I revised my answer several times and yet, I actually still forgot to correct the table alias πŸ˜… (in step 3 above).

Now I’ve corrected it, and in addition, I also used WP_Meta_Query::get_clauses() instead of hard-coding the (table) alias. Many thanks to @Artem and his other question!

Leave a Comment