Order Posts by Closest Numeric Values

Question

I'm using custom post types to display properties and have a custom price field assigned to each post. On the property page I want to display a list of four similarly priced properties, two less than or equal to the price of the current property and two greater than the current price. Ordering properties numerically by price is easy enough with wp_query, but how do I only display the closest values to the price of the current property?

Answer 1

One possibility is a direct SQL query similar to the one given here.

But I'm not convinced that it would be much more efficient than 2 queries, all handled by WordPress:

The is untested

$price =0; //Price of current property
$id=0; //Property (post) ID.

$args = array(
    'post_type' => 'property',
    'post__not_in'=>array($id),
    'posts_per_page'=> 2,
    'meta_query' => array(array(
        'key' => 'price',
        'value' => $price,
        'type' => 'numeric',
        'compare' => '>='
        )),
     'meta_key'=> 'price',
     'orderby'=>'meta_value_num',
     'order'=>'ASC'
    );

 $two_above = get_posts($args);

 //Put their IDs along with current property ID in an array
 $ids = array_values(wp_list_pluck($two_above, 'ID'));
 array_push($ids, $id);

 //Exclude returned properties and current property from next query
 $args['post__not_in'] = $ids;

 $args['meta_query']['compare'] = '<=';
 $args['order'] = 'DESC';

 $two_below = get_posts($args);

Answer 2

Edit

Thanks for calling me out, it's the only way to get better.

The updated query would look like this:

$args = array(
    'post_type' => 'POST',
    'meta_query' => array(
        'key' => 'PRICE',
        'value' => array( ($price - 2), ($price + 2)),
        'type' => 'numeric',
        'compare' => 'BETWEEN'
        );
    );

Thanks again, kaiser for making me do the right thing.

---

The quickest thing to do is to create your own query using $wpdb. The most basic function would look like this:

function my_custom_get ($price) {
    global $wpdb;
    $min_price = $price - 2;
    $max_price = $price + 2;

    $sql_query = "
        SELECT $wpdb->posts.* 
        FROM $wpdb->posts, $wpdb->postmeta
        WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
        AND $wpdb->postmeta.meta_key = 'PRICE' 
        AND $wpdb->postmeta.meta_value < $max_price
        AND $wpdb->postmeta.meta_value > $min_price
        AND $wpdb->posts.post_status = 'publish' 
        AND $wpdb->posts.post_type = 'PRODUCT'
        AND $wpdb->posts.post_date < NOW()
        ORDER BY $wpdb->posts.post_date DESC
        ";
    return $wpdb->get_results($querystr, OBJECT);
}

Don't forget to change the values of meta_key and post_type to fit your specific application. Hope this helps!