Make meta query treat meta_value as 1 or 0

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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 = "CASEn" .
                "  WHEN wp_postmeta.meta_value+0 > 0 THEN 1n" .
                "  WHEN wp_postmeta.meta_value+0 <= 0 THEN 2n" .
            "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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0