Multiple meta query - orderby custom field WordPress
- SOLVED
I'm using the following function to enable me to query multiple custom fields.
I'd like to add an order parameter to my query, to order the posts by another custom field value ("priority"). How do I do this?
function get_post_meta_multiple($metaDataList) {
global $wpdb;
$querystr = "SELECT p.* FROM $wpdb->posts AS p WHERE p.ID IN ( ";
$querystr .= "SELECT post_id FROM $wpdb->postmeta WHERE ";
$innerqry = array();
foreach($metaDataList as $key => $value) {
$innerqry[] = $wpdb->prepare( "(meta_key = %s AND meta_value = %s)", $key, $value );
}
$querystr .= implode(" OR ", $innerqry);
$querystr .= " GROUP BY post_id ";
$querystr .= "HAVING count(*) = " . count($metaDataList);
$querystr .= ") AND p.post_status = 'publish' ";
$metaResults = $wpdb->get_results($querystr, OBJECT);
return $metaResults;
}
Answers (6)
Oleg Butuzov answers:
is it number? you store in meta-value?
Dan Davies comments:
Yeah 1/2/3
Oleg Butuzov comments:
global $wp_query;
function priority_select($sql){
global $wpdb;
if (strpos($sql, 'proirity') !== false){
$sql = str_replace("ORDER BY {$wpdb->posts}.post_date", "ORDER BY CAST({$wpdb->postmeta}.meta_key AS UNSIGNED)", $sql);
}
return $sql;
}
add_filter('query', 'priority_select');
query_posts( array_merge(array('meta_key' => 'proirity', 'order' => 'asc'), $wp_query->query));
// loop code
wp_reset_query();
Oleg Butuzov comments:
ok... the main problem is
1) meta_value is a text, and its can't be a sorted like a number.
2) so left only to set a prober convertion before sorting...
next code is also valid.
global $wp_query;
function priority_select($sql){
global $wpdb;
if (strpos($sql, 'proirity') !== false){
$sql = str_replace("ORDER BY {$wpdb->postmeta}.meta_key", "ORDER BY CAST({$wpdb->postmeta}.meta_key AS UNSIGNED)", $sql);
}
return $sql;
}
add_filter('query', 'priority_select');
query_posts( array_merge(array('meta_key' => 'proirity', 'orderby'=> 'meta_value', 'order' => 'asc'), $wp_query->query));
// loop code
wp_reset_query();
all we do is only changing the column value for sorting
http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html
Oleg Butuzov comments:
ORDER BY CAST({$wpdb->postmeta}.meta_key AS UNSIGNED) ASC
or
ORDER BY CAST({$wpdb->postmeta}.meta_key AS UNSIGNED) DESC
is also can used in your own query... but.
i belive better to select post_id from meta tabble before you run your query....
and run after the your query substituting the subquery my sinple implode. and order by ordering in dataset.
need example how to order query by data set or not?
Oleg Butuzov comments:
OOPS for my case...
replace UNSIGNED by DECIMAL(9,2) pelase.
Dan Davies comments:
Sorry Oleg, but I'm not really sure how I integrate this code with my existing function.
Oleg Butuzov comments:
function get_post_meta_multiple($metaDataList) {
global $wpdb;
$querystr = "SELECT post_id FROM $wpdb->postmeta WHERE ";
$innerqry = array();
foreach($metaDataList as $key => $value) {
$innerqry[] = $wpdb->prepare( "(meta_key = %s AND meta_value = %s)", $key, $value );
}
$querystr .= implode(" OR ", $innerqry);
$querystr .= " GROUP BY post_id ";
$querystr .= "HAVING count(*) = " . count($metaDataList);
$querystr .= "HAVING count(*) = " . count($metaDataList);
$querystr .= "ORDER BY CAST({$wpdb->postmeta}.meta_value AS DECIMAL(9,2)) DESC";
$IDS = $wpdb->get_col($querystr);
//PART ONE
$querystr = "SELECT p.* FROM $wpdb->posts AS p WHERE p.ID IN (".implode(',', $IDS).") AND p.post_status = 'publish' order by field(p.post_id, '".implode("','", $IDS)."')";
$metaResults = $wpdb->get_results($querystr, OBJECT);
return $metaResults;
}
something like that... hasn't tested. because its fits by your values and data.
Svilen Popov answers:
function get_post_meta_multiple($metaDataList) {
global $wpdb;
$querystr = "SELECT p.* FROM $wpdb->posts AS p WHERE p.ID IN ( ";
$querystr .= "SELECT post_id FROM $wpdb->postmeta WHERE ";
$innerqry = array();
foreach($metaDataList as $key => $value) {
$innerqry[] = $wpdb->prepare( "(meta_key = %s AND meta_value = %s)", $key, $value );
}
$querystr .= implode(" OR ", $innerqry);
$querystr .= " GROUP BY post_id ";
$querystr .= "HAVING count(*) = " . count($metaDataList);
$querystr .= ") AND p.post_status = 'publish' ";
$querystr .= "ORDER BY wpostmeta.meta_value ASC";
$metaResults = $wpdb->get_results($querystr, OBJECT);
return $metaResults;
}
Dan Davies comments:
Unfortunately this breaks my query :(
Milan Petrovic answers:
You can't do this with a single query. For each meta_key value you need to sort by, you need a new query. All these queries need to be used in main query that will select from them. Problem is that meta_key is column, and it's value is need to sort the meta_value. Queries are needed to effectively convert meta_key and meta_value to new table that can be sorted.
Jignesh Patel answers:
Hello
I have done this before, but with much complicated one.
Also answered bit similar answer as well: http://www.wpquestions.com/question/show/id/517
For your case, try to use below customized function. It does the order by meta_value for meta_key 'Rental'. Let me know if any problem.
function get_post_meta_multiple($metaDataList) {
global $wpdb;
$querystr = "SELECT p.* FROM $wpdb->posts AS p ";
$querystr = " LEFT join $wpdb->postmeta as pm on p.ID = pm.post_id ";
$querystr .= "WHERE p.ID IN ( ";
$querystr .= "SELECT post_id FROM $wpdb->postmeta WHERE ";
$innerqry = array();
foreach($metaDataList as $key => $value) {
$innerqry[] = $wpdb->prepare( "(meta_key = %s AND meta_value = %s)", $key, $value );
}
$querystr .= implode(" OR ", $innerqry);
$querystr .= " GROUP BY post_id ";
$querystr .= "HAVING count(*) = " . count($metaDataList);
$querystr .= ") AND p.post_status = 'publish' ";
$querystr .= " AND pm.meta_key='Rental' ORDER BY pm.meta_value";
$metaResults = $wpdb->get_results($querystr, OBJECT);
return $metaResults;
}