I spent most of the day today trying to figure out how to sort and search WordPress post meta data. I did a lot of Google searches trying to figure this out. I found just enough to piece it together. Here it is.
if (!is_admin()) {
add_filter('posts_clauses', 'ors_rental_query');
}
function ors_rental_query($clauses) {
if (!strstr($clauses['where'], 'rental')) {
return $clauses;
}
global $wpdb, $ors_rental_cookies;
// Secure database fields with standardized CAST statements
$clauses['fields'] .= ", CAST((select {$wpdb->postmeta}.meta_value from {$wpdb->postmeta} where {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID and {$wpdb->postmeta}.meta_key = 'price') as decimal) as price";
$clauses['fields'] .= ", CAST((select {$wpdb->postmeta}.meta_value from {$wpdb->postmeta} where {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID and {$wpdb->postmeta}.meta_key = 'home_size') as decimal) as home_size";
$clauses['fields'] .= ", CAST((select {$wpdb->postmeta}.meta_value from {$wpdb->postmeta} where {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID and {$wpdb->postmeta}.meta_key = 'bedrooms') as decimal) as bedrooms";
$clauses['fields'] .= ", CAST((select {$wpdb->postmeta}.meta_value from {$wpdb->postmeta} where {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID and {$wpdb->postmeta}.meta_key = 'bathrooms') as decimal) as bathrooms";
$clauses['having'] = array();
$clauses['orderby'] = '';
// Sanitize the text search using esc_sql() to block bad query inputs
if (isset($ors_rental_cookies['text_search']) && $ors_rental_cookies['text_search'] !== '') {
$text_search = esc_sql(sanitize_text_field(stripslashes($ors_rental_cookies['text_search'])));
$clauses['where'] .= " and ({$wpdb->posts}.post_title like '%{$text_search}%'";
$clauses['where'] .= " or {$wpdb->posts}.post_content like '%{$text_search}%')";
}
// Cast parameter limits strictly to float/int to eliminate script insertion risks
$search_params = array('bedrooms', 'bathrooms');
foreach ($search_params as $param) {
if (isset($ors_rental_cookies[$param]) && $ors_rental_cookies[$param] !== '') {
$param_value = (float)$ors_rental_cookies[$param];
$clauses['having'][] = "$param = '$param_value'";
}
}
if (!empty($clauses['having'])) {
$clauses['where'] .= ' HAVING ' . implode(' and ', $clauses['having']);
}
// Cast mathematical calculation parameters to numeric types
$order_params = array('price' => 'price_near', 'home_size' => 'size_near');
foreach ($order_params as $field => $param) {
if (isset($ors_rental_cookies[$param]) && $ors_rental_cookies[$param] !== '') {
$param_value = (float)$ors_rental_cookies[$param];
$clauses['orderby'] .= ", ABS({$param_value} - $field)";
}
}
if ($clauses['orderby'] == '') {
$clauses['orderby'] = 'price ASC';
} else {
$clauses['orderby'] = substr($clauses['orderby'], 2);
}
return $clauses;
}
New in WordPress 3.1 is the posts_clauses filter. There is no documentation page on it, so I had to Google for the usage. But it works awesome for what I needed.
I use the $clauses['fields'] to subquery in the meta data. Then I'm able to do sort orders on it. I can order by price or whatever from the aliased subqueries. In my case I use special "near" method to find the prices that are near the search param.
To find an exact match like in my bathrooms and bedrooms, I use a HAVING clause to get access to my aliases. I just tack it to the end of the $clauses['where'] and it works like a charm.
I make sure to use CAST to cast my strings to integers for sorting by price and size. No buggy string number sorts here.
The above code does a lot and it might be more than you need but it should point you in the right direction. For more check out the source code Rental Post Type Line 293
I have had problems shifting into neutral since just after getting the bike. I chocked it up to needed adjustment. One day I started playing with the adjustment and I just could not dial it in.