I've created a custom post field for use on my site that is the price of a real estate item for sale (that is the subject of the post). It should sort posts archives by price but I cannot get it to sort high to low; it still sorts as follows:
$2,900,000
$24,450,000
$1,900,000
$13,000,000

The client will be adding posts so it needs to be easy for them to enter the value as dollars.

I've tried casting the input as UNSIGNED and also as NUMERIC and have tried other things such as meta_key_num instead of meta_key.

Here is what I have currently, which "almost" works.

add_action( 'pre_get_posts', 'kr_change_posts_order' ); 
// start - from Toolset
function func_orderby_asking_price( $orderby ) {
    global $WP_Views;

     if($WP_Views) {
         $orderby = str_replace( 'wp_postmeta.meta_value', "cast(replace(trim( leading '$'  from wp_postmeta.meta_value),',','')  AS UNSIGNED)", $orderby ); // also tried NUMERIC
    }

    return $orderby;
}
add_filter('posts_orderby', 'func_orderby_asking_price' );
// end from Toolset

function kr_change_posts_order( $query ) {
    if ( $query->is_main_query() && !is_admin() ) {
        $orderby = genesis_get_custom_field( 'price' );
        $query->set( 'cat', ( array( 5, 6, 15 ) ) );
        $query->set( 'meta_key', 'price' );
        $query->set( 'orderby', 'meta_value' ); // also tried meta_value_num
        $query->set( 'order', 'DESC' );
    }
}

These are actually two pieces of code (functions) that I found and modified. I've also tried keeping the functions separated instead of one within the other, but so far have not found a solution to get the sorting to work correctly.

I have looked for a solution on this site and elsewhere for a couple of days but no luck.

Thanks in advance for your help.

KJR

I've created a custom post field for use on my site that is the price of a real estate item for sale (that is the subject of the post). It should sort posts archives by price but I cannot get it to sort high to low; it still sorts as follows:
$2,900,000
$24,450,000
$1,900,000
$13,000,000

The client will be adding posts so it needs to be easy for them to enter the value as dollars.

I've tried casting the input as UNSIGNED and also as NUMERIC and have tried other things such as meta_key_num instead of meta_key.

Here is what I have currently, which "almost" works.

add_action( 'pre_get_posts', 'kr_change_posts_order' ); 
// start - from Toolset
function func_orderby_asking_price( $orderby ) {
    global $WP_Views;

     if($WP_Views) {
         $orderby = str_replace( 'wp_postmeta.meta_value', "cast(replace(trim( leading '$'  from wp_postmeta.meta_value),',','')  AS UNSIGNED)", $orderby ); // also tried NUMERIC
    }

    return $orderby;
}
add_filter('posts_orderby', 'func_orderby_asking_price' );
// end from Toolset

function kr_change_posts_order( $query ) {
    if ( $query->is_main_query() && !is_admin() ) {
        $orderby = genesis_get_custom_field( 'price' );
        $query->set( 'cat', ( array( 5, 6, 15 ) ) );
        $query->set( 'meta_key', 'price' );
        $query->set( 'orderby', 'meta_value' ); // also tried meta_value_num
        $query->set( 'order', 'DESC' );
    }
}

These are actually two pieces of code (functions) that I found and modified. I've also tried keeping the functions separated instead of one within the other, but so far have not found a solution to get the sorting to work correctly.

I have looked for a solution on this site and elsewhere for a couple of days but no luck.

Thanks in advance for your help.

KJR

• posts
• wp-query
• post-meta
• pre-get-posts

• 1 Prices really shouldn't be stored with $ and ,. You will have a considerably easier time if you save them as numbers, and format them only on output. – Jacob Peattie Commented Feb 27, 2020 at 23:28
• Hi @Jacob, I have to make it easy for the client. With the large number of zeroes, without the commas, they are bound to make mistakes. – KJRwebdev Commented Feb 28, 2020 at 1:49
• 1 The best way to handle that would be to sanitise it to a pure number before saving it, and just formatting it when displayed on the editing screen. – Jacob Peattie Commented Feb 28, 2020 at 2:05
• Hi, I have absolutely no idea how to do that. I'm not much better than a novice at coding in PHP. – KJRwebdev Commented Feb 28, 2020 at 4:01

1 Answer 1

Thanks to Brad Dalton at wpsites for getting me to the answer.

1. Install Advanced Custom Fields plugin. Create a custom field called price, formatted as a number.

2. Use the code below (based on Bill Erickson's tutorial):

add_action( 'pre_get_posts', 'kr_change_posts_order' ); 
function kr_change_posts_order( $query ) {
    if ( $query->is_main_query() && !is_admin() && !is_page() ) {
        $orderby = genesis_get_custom_field( 'price' );
        $query->set( 'cat', ( array( 5, 6, 15 ) ) );
        $query->set( 'meta_key', 'price' );
        $query->set( 'orderby', 'meta_value_num' );
        $query->set( 'order', 'DESC' );
    }
}

This sorts by price, descending, for three post categories with IDs 5, 6 and 15.