18

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?

5
  • try changing 'orderby' => 'meta_value_num', to 'orderby' => 'meta_value_num meta_value',
    – Bainternet
    Mar 13, 2012 at 11:51
  • Thanks but that does not work :(
    – Shaun
    Mar 13, 2012 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, 2012 at 13:24
  • 1
    posted as an answer for people who don't read comments.
    – Bainternet
    Mar 13, 2012 at 13:26
  • POA means "price on asking" en.wikipedia.org/wiki/Price_on_application
    – sudip
    Mar 7, 2018 at 8:46

2 Answers 2

28

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',
2
  • 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, 2014 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.
    – Yatix
    Aug 19, 2016 at 12:43
3

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 and acknowledge you have read our privacy policy.

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