Using Orderby and meta_value_num to order numbers first then strings

The question:

I have a list of products, each with a price in a custom field stored as text such as “2.50” or “5.00” and I am displaying them on the page with a custom query that sorts by the price:

    if(!$wp_query) {
        global $wp_query;
    }

    $args = array(
        'meta_key' => 'price',
        'orderby' => 'meta_value_num',
        'order' => 'ASC'
    );

    query_posts( array_merge( $args , $wp_query->query ) );

This works fine for the prices, but some prices are “POA” and I would like to show them last, however the above orders in such a way that “POA” is shown first.

Is there any way to alter this, or a quick hack I could use to sort the array afterwards and put any “POA” prices last?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

The OrderBy argument can take more then one parameter so the solution was to change :

'orderby' => 'meta_value_num',

to:

'orderby' => 'meta_value meta_value_num',

Method 2

I found this solution by combining code by @bonger and https://stackoverflow.com/questions/18084199/wordpress-query-order-by-case-when

And it works well.

Function

function filter_case($orderby = '') {
  $orderby .= "CASE WHEN wp_postmeta.meta_value RLIKE '^[0-9]' THEN '' ELSE wp_postmeta.meta_value END ASC, wp_postmeta.meta_value+0 ASC";
  return $orderby;
}

Before Query

add_filter( 'posts_orderby', 'filter_case' );

$wp_query = new WP_Query($args);

remove_filter( 'posts_orderby', 'filter_case' );


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

Leave a Comment