My products have no in-stock status, just a quantity in stock. Is there any way to make meta query to treat its value as 0 or 1? Like, first the values that are not 0, then that are 0.

My query as it is right now:

'meta_query' => array(
   'relation' => 'AND',
   'quantity_total' => array(
            'key'     => 'total_quantity',
            'type'    => 'NUMERIC',
            'compare' => 'EXISTS',
   ),
   'price_lowest_first' => array(
            'key'     => 'main_price',
            'type'    => 'NUMERIC',
            'compare' => 'EXISTS',
    )
),
'orderby' => array(
   'quantity_total' => 'DESC',
   'price_lowest_first' => 'ASC'
)

EDIT:

For now, I ended up using an 'in-stock' flag, and here's my code:

//sort by total quantity
if($sort !== '4'){      
    $args['meta_query']['in_stock'] = array(
            'key' => '1C_in_stock',
           'type' => 'NUMERIC',
        'compare' => 'EXISTS'
    );
    $args['orderby']  = array( 
        'in_stock' => 'DESC'
    );
}

//apply sort preferences to arguments
switch($sort){
    #by stock status
    case '1':
        // '1' is default, do not want to move it to the default clause yet.
        break;

   #sort by price: lower first
    case '2':
        $args['meta_query']['price_lowest_first'] = array(
                'key' => '1C_price'
               'type' => 'NUMERIC',
            'compare' => 'EXISTS'
        );  
        $args['orderby']['price_lowest_first']  = 'ASC';
        break;

    #sort by price:higher first
    case '3':
        $args['meta_query']['price_highest_first'] = array(
            'key' => '1C_price',
            'type'    => 'NUMERIC',
            'compare' => 'EXISTS',
        );
        $args['orderby']['price_highest_first']  = 'DESC';
        break;

    #sort randomly
    case '4':
        $args['orderby'] = 'rand';
        break;
}

But is there a query to order items by non-zero and zero quantity? And then, order each group by price.

My products have no in-stock status, just a quantity in stock. Is there any way to make meta query to treat its value as 0 or 1? Like, first the values that are not 0, then that are 0.

My query as it is right now:

'meta_query' => array(
   'relation' => 'AND',
   'quantity_total' => array(
            'key'     => 'total_quantity',
            'type'    => 'NUMERIC',
            'compare' => 'EXISTS',
   ),
   'price_lowest_first' => array(
            'key'     => 'main_price',
            'type'    => 'NUMERIC',
            'compare' => 'EXISTS',
    )
),
'orderby' => array(
   'quantity_total' => 'DESC',
   'price_lowest_first' => 'ASC'
)

EDIT:

For now, I ended up using an 'in-stock' flag, and here's my code:

//sort by total quantity
if($sort !== '4'){      
    $args['meta_query']['in_stock'] = array(
            'key' => '1C_in_stock',
           'type' => 'NUMERIC',
        'compare' => 'EXISTS'
    );
    $args['orderby']  = array( 
        'in_stock' => 'DESC'
    );
}

//apply sort preferences to arguments
switch($sort){
    #by stock status
    case '1':
        // '1' is default, do not want to move it to the default clause yet.
        break;

   #sort by price: lower first
    case '2':
        $args['meta_query']['price_lowest_first'] = array(
                'key' => '1C_price'
               'type' => 'NUMERIC',
            'compare' => 'EXISTS'
        );  
        $args['orderby']['price_lowest_first']  = 'ASC';
        break;

    #sort by price:higher first
    case '3':
        $args['meta_query']['price_highest_first'] = array(
            'key' => '1C_price',
            'type'    => 'NUMERIC',
            'compare' => 'EXISTS',
        );
        $args['orderby']['price_highest_first']  = 'DESC';
        break;

    #sort randomly
    case '4':
        $args['orderby'] = 'rand';
        break;
}

But is there a query to order items by non-zero and zero quantity? And then, order each group by price.

• wp-query
• meta-query

• You're using 'type' => 'NUMERIC' in your meta query clauses, so the meta value is already being treated as a number, but you would want to use the DECIMAL type for the price meta. Or am I not understanding what you mean? And what problem you're having with your (meta) query? Are the posts not getting sorted properly? If so, can you post sample data and the outcome you're currently getting and the one you're hoping to get? – Sally CJ Commented Feb 7, 2021 at 8:24
• Hey Sally! Thank you for stopping by! My posts get ordered properly, but that's not what I want )) . What I want is the system to treat product quantity like it's either 0 or 1. That is, I want the products with any quantitiy to be shown first, ordered by price (regardless of actual quantity), and then, the ones with the quantity of 0, ordered by price again. I could not figure it out and ended up writing an 'in-stock' flag into my database. I wonder if it's possible at all, like divide into 2 groups by non-zero-quantity and then order each of them by price, without the flag. – Artem Commented Feb 8, 2021 at 9:41
• I've added my solution to the question, alhough it doesn't really answer it. – Artem Commented Feb 8, 2021 at 9:49

1 Answer 1

If I understand it correctly..

What you're looking for can be achieved using the CASE statement in MySQL, where you would use the following in the ORDER BY clause:

# group the quantity meta into two groups
CASE
    # group one - all quantities that are 1 or more; we flag them as "1"
    WHEN wp_postmeta.meta_value+0 > 0 THEN 1

    # group two - all quantities that are 0 or less; we flag them as "2"
    WHEN wp_postmeta.meta_value+0 <= 0 THEN 2
END ASC

Where wp_postmeta.meta_value is the value of the total_quantity meta which is the first one in your meta query, and the +0 means we're casting the meta value as a number.

And to add that CASE statement to the ORDER BY clause, you can use the posts_orderby hook. Here's an example using closure with a private variable:

// *Private variable used with the closure below.
$_do_filter = true;

add_filter( 'posts_orderby',
    function ( $orderby ) use ( &$_do_filter ) {
        if ( $_do_filter ) {
            $orderby = "CASE\n" .
                "  WHEN wp_postmeta.meta_value+0 > 0 THEN 1\n" .
                "  WHEN wp_postmeta.meta_value+0 <= 0 THEN 2\n" .
            "END ASC, $orderby";
        }

        return $orderby;
    }
);

$query = new WP_Query( array(
    'meta_query' => array(
        'relation'          => 'AND',
        'quantity_total'    => array(
            'key'     => 'total_quantity',
            'type'    => 'NUMERIC',
            'compare' => 'EXISTS',
        ),
        'price_lowest_first' => array(
            'key'     => 'main_price',
            'type'    => 'DECIMAL(5, 2)',
            'compare' => 'EXISTS',
        ),
    ),
    'orderby'    => array(
        // we already have the CASE statement, so this is no longer needed
//      'quantity_total'     => 'DESC',
        'price_lowest_first' => 'ASC',
    ),
    // ... your other args.
) );

// Disable the filter.
$_do_filter = false;

// Then run your loop here..
// while ( $query->have_posts() ) ...

And note that for the main_price meta, I set the type to DECIMAL(5, 2) so that the prices would be sorted by taking into account the decimal.