16

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?

  • try changing 'orderby' => 'meta_value_num', to 'orderby' => 'meta_value_num meta_value', – Bainternet Mar 13 '12 at 11:51
  • Thanks but that does not work :( – Shaun Mar 13 '12 at 13:23
  • Aha! But the other way around does work, meta_value meta_value_num ! Thanks! Do you want to write up an answer so I can vote it up? – Shaun Mar 13 '12 at 13:24
  • 1
    posted as an answer for people who don't read comments. – Bainternet Mar 13 '12 at 13:26
  • POA means "price on asking" en.wikipedia.org/wiki/Price_on_application – sudip Mar 7 '18 at 8:46
23

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',
  • 3
    This just sorts alphabetically, as the second parameter is a no-op, sorting on the same field to no effect. To do this so that the numbers sort numerically and the alphas alphabetically you'd have to use a filter to customize the orderby to use some sort of SQL casing, eg ORDER BY CASE WHEN wp_postmeta.meta_value RLIKE '^[0-9]' THEN '' ELSE wp_postmeta.meta_value END ASC, wp_postmeta.meta_value+0 ASC – bonger Nov 5 '14 at 9:04
  • @bonger Thank you! Your solution is complete solution for this problem. And here is rest of the code where your query is placed. I am going to post it as answer for new visitors. – Gangesh Aug 19 '16 at 12:43
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' );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.