1

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?

4
  • Why can't you enter a number without a dollar sign and then display the dollar sign on the front-end if needed? I am not sure, but I suspect this would be a very complicated query. Jul 26, 2014 at 1:27
  • I understand but all custom fields are created by a plugin and inputs these with a $ sign, and the database already has a lot of posts.
    – coder
    Jul 26, 2014 at 2:48
  • 1
    A bad plugin, I'm afraid. :( Jul 26, 2014 at 6:56
  • I agree with Mayeenul. I would contact the developer and ask him/her to stop saving the $. Jul 26, 2014 at 13:57

2 Answers 2

3

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' );
1
  • I tested but it won't order them correctly, I will test it again with the exact query your posted because mine has some other arguments too.
    – coder
    Jul 26, 2014 at 13:02
0

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.

3
  • This needs into functions.php? I tested but it won't order them correctly.
    – coder
    Jul 26, 2014 at 13:05
  • Does it order it the same way as before? Jul 26, 2014 at 13:25
  • Yes. Maybe a solution would be to regenerate a new custom field using your filtering in functions.php and then use that as the meta_key to sort on. Does your code work at your end? Maybe some custom code in my theme prevents it to work.
    – coder
    Jul 26, 2014 at 16:03

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.