How to orderby meta_value_num with dollar ($) sign

Question

How to orderby meta_value_num with dollar ($) sign

coder

2014年7月26日 08:02

The custom fields all have prices including $ signs, and a dot as separator.

$theQuery = new WP_Query(array(
'orderby' = 'meta_value_num',
'meta_key' = 'price',
'order' = ASC  
));

All formatted like this:

$24.95 $190.00 $1.40

They won't sort correctly due to the dollar sign, is it possible to ignore or remove the first character / $ sign in this WP_Query?

Topic wp-parse-args meta-query wp-query query sort Wordpress

Category Web

bonger · Accepted Answer · 2014年7月26日 08:02

You could also use the posts_orderby filter:

function wpse155827_posts_orderby_price( $orderby ) {
    return str_replace( 'wp_postmeta.meta_value', 'substr(wp_postmeta.meta_value, 1)', $orderby );
}
add_filter( 'posts_orderby', 'wpse155827_posts_orderby_price' );
$theQuery = new WP_Query( array(
    'orderby' => 'meta_value_num',
    'meta_key' => 'price',
    'order' => 'ASC',
    'suppress_filters' => false,
) );
remove_filter( 'posts_orderby', 'wpse155827_posts_orderby_price' );

Manny Fleurmond · Accepted Answer · 2014年7月26日 04:55

You'll have to use the post_clauses filter to create some custom SQL:

add_filter( 'posts_clauses', 'wpse155827_price_sort', 10, 2 );

function wpse155827_price_sort( $clauses, $wp_query ) {
    $orderby = $wp_query->get( 'orderby' ); 
    $order = ( $wp_query->get( 'order' ) == 'desc') ? 'DESC' : 'ASC';
    if( 'price' === $orderby ) {
        $clauses['join'] .= " LEFT JOIN {$wpdb->postmeta} price ON( {$wpdb->posts }.ID = price.post_id AND price.meta_key = 'price') ";
        $clauses['orderby'] = " CONVERT( REPLACE(price.meta_value, '$', ''), DECIMAL(13,2) ) " . $order;
    }

    return $clauses;
}

Basically, this is a filter that creates custom mySQL code in your query whenever you set orderby to price. in the mySQL code, it takes the meta value, removes the $ sign, them converts it into a decimal. This will allow it to sort properly now.

How to orderby meta_value_num with dollar ($) sign

About