Custom query sorted on two meta fields WordPress
- REFUNDED
Hi, guys. I have a custom post type with two meta fields: "city" and "price". I want to list all posts ordered first by city and then by price even if the city and price fields are not filled in.
This query is VERY close. It returns all posts if both city and price are filled in but doesn't honor the LEFT JOIN element and include posts even if they don't have a city and price value.
SELECT
p.*
FROM
$wpdb->posts p
LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_ID
LEFT JOIN $wpdb->postmeta pm2 ON p.ID = pm2.post_ID
WHERE
p.post_status = 'publish' AND
p.post_type = 'property' AND
p.post_date < NOW() AND
pm.meta_key = 'city' AND
pm2.meta_key = 'price'
ORDER BY
pm.meta_value ASC,
pm2.meta_value DESC,
p.post_title ASC
What am I doing wrong?
Thanks!
Answers (3)
Arnav Joy answers:
so what to do you want in case if there is no value in price and city meta field.
can you show it by giving an example or screenshot..
Kevin McGillivray comments:
Well, in this example, it doesn't matter. I'm not returning the values for price and city in the query; I'm just using them for sorting. But if I added those fields to the query, I should get null values in city and/or price with the rest of the post data for each post that matches the rest of the criteria. At least that's what I *think* it should do.
Here's a link to w3school's LEFT JOIN explanation and it shows an example:
[[LINK href="http://www.w3schools.com/sql/sql_join_left.asp"]]http://www.w3schools.com/sql/sql_join_left.asp[[/LINK]]
Thanks!
Kevin
Arnav Joy comments:
try this
SELECT
p.*
FROM
$wpdb->posts p
LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_ID
LEFT JOIN $wpdb->postmeta pm2 ON p.ID = pm2.post_ID
WHERE
p.post_status = 'publish' AND
p.post_type = 'property' AND
p.post_date < NOW() OR
pm.meta_key = 'city' OR
pm2.meta_key = 'price'
ORDER BY
pm.meta_value ASC,
pm2.meta_value DESC,
p.post_title ASC
Arnav Joy comments:
try it
SELECT DISTINCT
p.*
FROM
$wpdb->posts p
LEFT JOIN $wpdb->postmeta pm ON p.ID = pm.post_ID
LEFT JOIN $wpdb->postmeta pm2 ON p.ID = pm2.post_ID
WHERE
p.post_status = 'publish' AND
p.post_type = 'property' AND
p.post_date < NOW() OR
pm.meta_key = 'city' OR
pm2.meta_key = 'price'
ORDER BY
pm.meta_value ASC,
pm2.meta_value DESC,
p.post_title ASC
Kevin McGillivray comments:
Neither of your suggestions worked. I figured this out on my own and posted the correct code in another thread below. Thanks.
Kannan C answers:
If i correctly understand, you need to use OR in your where condition.
WHERE
p.post_status = 'publish' AND
p.post_type = 'property' AND
p.post_date < NOW() AND
pm.meta_key = 'city' OR
pm2.meta_key = 'price'
Kevin McGillivray comments:
Sorry, Kannan. That's not the issue. I have this same problem even if I only try to join to post_meta once to deal with just, say, the price field. Thanks!
Kannan C comments:
Can you detail, what are you trying to output, what's wrong in your output?
Kannan C comments:
FYI: if you want to list all posts without filtering by meta keys, simply use
WHERE
p.post_status = 'publish' AND
p.post_type = 'property'
ORDER BY
pm.meta_value ASC,
pm2.meta_value DESC,
p.post_title ASC
Kevin McGillivray comments:
The problem is that if either the city or price value for a particular post is empty, that post is left out of the results entirely when it should be included in the results.
Also, I'm not interested in listing all posts without filtering my meta keys. That's not related in any way to my question.
Kannan C comments:
Then you should create empty meta keys even it's value is not filled. You can use something like this
add_action('save_post', 'save_meta_details');
function save_meta_details(){
global $post;
if ( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE )
return;
$city= trim($_POST["city"]);
$price= trim($_POST["price"]);
update_post_meta($post->ID, "city", $city);
update_post_meta($post->ID, "price", $price);
}
however, by doing so, all your posts of type 'property' can have the above meta keys, by which result will return all posts from 'property'. Am i right?
Kevin McGillivray comments:
That's just unnecessary overhead since that's the whole purpose of the LEFT JOIN clause in SQL.
I figured this out on my own. Apparently the problem was that I was limiting meta_key in the WHERE clause instead of in the LEFT JOIN clause.
If anyone is interested, here's the complete query:
SELECT
p.*, pm.meta_value, pm2.meta_value
FROM
$wpdb->posts p
LEFT JOIN $wpdb->postmeta pm ON
p.ID = pm.post_ID AND
pm.meta_key = 'city'
LEFT JOIN $wpdb->postmeta pm2 ON
p.ID = pm2.post_ID AND
pm.meta_key = 'price'
WHERE
p.post_status = 'publish' AND
p.post_type = 'property' AND
p.post_date < NOW()
ORDER BY
pm.meta_value ASC,
p.post_title ASC
Kevin McGillivray comments:
I found a couple typos. Here's the corrected version:
SELECT
p.*, pm.meta_value, pm2.meta_value
FROM
$wpdb->posts p
LEFT JOIN $wpdb->postmeta pm ON
p.ID = pm.post_ID AND
pm.meta_key = 'city'
LEFT JOIN $wpdb->postmeta pm2 ON
p.ID = pm2.post_ID AND
pm2.meta_key = 'price'
WHERE
p.post_status = 'publish' AND
p.post_type = 'property' AND
p.post_date < NOW()
ORDER BY
pm.meta_value ASC,
pm2.meta_value DESC
Fahad Murtaza answers:
I would suggest a more generic approach and a function which you can use again and again without any problems. Though the joins will slow down your performance but it works
# ------------------------------------------------------------
// GET POST META VALUE FUNCTION
// By Jamie Oastler http://idealienstudios.com/blog/wordpress/custom-field-query-quandry
// Modified by Matt Varone http://www.mattvarone.com/web-design/query-multiple-meta-values
# ------------------------------------------------------------
/*
Function variables:
$aMetaDataList(array) example: array('meta_key' => 'meta_value','meta_key_2' => 'meta_value_2 )
$szType(string) = "post" or "page"
$szCategory(string) = example: '1' or '1,2,3'
$iLimit(integer)
*/
function get_post_meta_multiple( $aMetaDataList = array(), $szType = 'post', $szCategory = NULL, $iLimit = NULL )
{
global $wpdb;
$szQuerystr = "SELECT p.* FROM $wpdb->posts AS p";
if ( $szCategory != NULL AND is_string($szCategory) )
{
$szQuerystr .= " LEFT JOIN $wpdb->term_relationships ON (p.ID = $wpdb->term_relationships.object_id) ";
$szQuerystr .= " LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) ";
}
$szQuerystr .= " WHERE p.ID IN ( ";
$szQuerystr .= "SELECT post_id FROM $wpdb->postmeta WHERE ";
$aInnerqry = array();
foreach($aMetaDataList as $szKey => $szValue)
{
$aInnerqry[] = $wpdb->prepare( "(meta_key = %s AND meta_value = %s)", $szKey, $szValue );
}
$szQuerystr .= implode(" OR ", $aInnerqry);
$szQuerystr .= " GROUP BY post_id ";
$szQuerystr .= "HAVING count(*) = " . count($aMetaDataList);
$szQuerystr .= ") AND p.post_status = 'publish' AND p.post_type = '".$szType."'";
if ( $szCategory != NULL AND is_string($szCategory) )
$szQuerystr .= " AND $wpdb->term_taxonomy.term_id IN(".$szCategory.")";
$szQuerystr .= " ORDER BY p.post_title ASC";
if ( $iLimit != NULL AND is_int($iLimit) )
$szQuerystr .= " LIMIT ".$iLimit;
// echo $szQuerystr;
$aMetaResults = $wpdb->get_results($szQuerystr, OBJECT);
return $aMetaResults;
}
I use it all the time.
Kevin McGillivray comments:
Thanks, Fahd. I've already found a solution but thanks for submitting.
Kevin