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.