Order by nested named meta queries with 'exists' and 'not exists'

Question

Each of my posts (online store items) has meta keys:

1. tdlrm_mp - my own menu_position (goes from 1 up and, unlike the wp_posts menu_position, may not exist)
2. 1C_quantity_total - number of items in stock

I'd like the items to be output in the following order:

1. first, the ones where tdlrm_mp exists, ordered by tdlrm_mp lowest to highest
2. then, the ones where tdlrm_mp does not exist, ordered by 1C_quantity_total, highest to lowest

Here are the arguments for my meta query:

$args['meta_query'] = array(
                'tdlrm_mp_key' => array(
                    'relation' => 'OR',
                    'tdlrm_mp_exists' => array(
                        'key'     => 'tdlrm_mp',
                        'type'    => 'NUMERIC',
                        'compare' => 'EXISTS',
                    ),
                    'tdlrm_mp_not_exists' => array(
                        'key'     => 'tdlrm_mp',
                        'compare' => 'NOT EXISTS',
                    )
                ),
                'quantity_total' => array(
                    'key'     => '1C_quantity_total',
                    'type'    => 'NUMERIC',
                    'compare' => 'EXISTS',
                ),
            );
            $args['orderby'] = array(
                'tdlrm_mp_key' => 'ASC',
                'quantity_total' => 'DESC'
            );

This doesn't seem to work, the posts get ordered by quantity only. What am I doing wrong here?

I tried plain

$args['orderby'] = array(
   'tdlrm_mp' => 'ASC',
   '1C_quantity_total' => 'DESC',
);

But it first outputs the items where tdlrm_mp does not exist

I've also found this question , but can't figure out if it applies here

Answer 1

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!