Custom WordPress SQL query for multiple meta values
To check for multiple custom fields you have to join the meta table twice.
<?php
global $wpdb;
$query = "
SELECT *
FROM {$wpdb->prefix}posts
INNER JOIN {$wpdb->prefix}postmeta m1
ON ( {$wpdb->prefix}posts.ID = m1.post_id )
INNER JOIN {$wpdb->prefix}postmeta m2
ON ( {$wpdb->prefix}posts.ID = m2.post_id )
WHERE
{$wpdb->prefix}posts.post_type = 'post'
AND {$wpdb->prefix}posts.post_status = 'publish'
AND ( m1.meta_key = 'date' AND m1.meta_value > '2010-12-05 00:00:00' )
AND ( m1.meta_key = 'date' AND m1.meta_value < '2010-12-12 00:00:00' )
AND ( m2.meta_key = 'some_other_meta_value' AND m2.meta_value != '' )
GROUP BY {$wpdb->prefix}posts.ID
ORDER BY {$wpdb->prefix}posts.post_date
DESC;
";
?>
While searching for a solution for a sort criteria for custom fields I found this article. Does not answer this particular question but another one. Thanks for the work :-).
As for the sort order of custom fields - I am still searching for an alternative to the date, key or value field - something like position number is what I need...
It think you'll find an answer on http://dev.mysql.com/doc/refman/5.1/en/order-by-optimization.html
Maybe a simple ORDER BY m1.meta_value might just do the trick, but I'm just guessing :-)
Great!!! :D
this is just what I was looking for...
now.. how about this.. but in a category...
i tried this
<code>
global $wpdb;
$query = "
SELECT *
FROM {$wpdb->prefix}posts
INNER JOIN {$wpdb->prefix}postmeta m1
ON ( {$wpdb->prefix}posts.ID = m1.post_id )
INNER JOIN {$wpdb->prefix}postmeta m2
ON ( {$wpdb->prefix}posts.ID = m2.post_id )
LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE
{$wpdb->prefix}posts.post_type = 'post'
AND {$wpdb->prefix}posts.post_status = 'publish'
AND ( m1.meta_key = 'Fecha Inicio' AND m1.meta_value = '".$hoy."' )
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->term_taxonomy.term_id IN(10)
GROUP BY {$wpdb->prefix}posts.ID
ORDER BY {$wpdb->prefix}posts.post_date
DESC;
";
</code>
lol.. :$ but it didn't work
thanks in advance.
Oh well, sql makes my head spin. I'm not even sure what you're trying to do. :-)
yeah.. makes my head spin too... hehe
I'm trying to query for multiple meta values (as in your code that worked just great!) but limited to just a Category
:)
HI nice code! Do you know of it's possible to order the result by the first meta_value like this :
ORDER BY CAST($wpdb->postmeta.meta_value AS BINARY) ASC";
thanks!
Just wanted to say thank you for this post, been trying all day to get my query right and 5 minutes after seeing this, all is good!
Thank You!
Thank you very much for your very helpful post.
Hello,
Can anyone help me? I have been up for a day and half trying figure this out. I am a newbie so i'm totally self taught. with that being said can someone tell me how get this code to work? I would like it so that once the event date has passed the even no longer shows.I want the events to be sorted by the custom event_date field and then not be shown once the event_date has passed.
I enter the event_date format 03/14/2011
Below is the code is have. I'm using it and a function for all the archive category pages. Could someone please please help me?
-----------------------------------------
add_filter('posts_join', 'new_join' );
function new_join($pjoin){
if(is_category()){
global $wpdb;
$pjoin .= "LEFT JOIN (
SELECT *
FROM $wpdb->postmeta
WHERE meta_key = 'event_date'
AND STR_TO_DATE(meta_value,'%m/%d/%Y %H:%i:%s') >= CURDATE() ) AS metasort
ON $wpdb->posts.ID = metasort.post_id";
}
return ($pjoin);
}
add_filter('posts_orderby', 'new_order' );
function new_order( $orderby ){
global $wpdb;
if(is_category()){
$orderby = "metasort.meta_value, '%m/%d/%Y %H:%i:%s' ASC";
}
return $orderby;
}
Your code helped me very much.
Have you ever had an issue with a query not returning any results for items that exist in the database?
Thanks.
"SELECT * FROM " . $wpdb->prefix . "cp_ad_pop_total a "
. "INNER JOIN " . $wpdb->posts . " p ON p.ID = a.postnum LEFT JOIN ". $wpdb->postmeta. "pm on p.ID =pm.post_id WHERE post_status = 'publish' AND post_type = '".APP_POST_TYPE."' AND pm.metakey='Boost_ad' and pm.metavalue!=''""
. "ORDER BY p.post_date DESC"
I not using SQL at all, but I'm trying to display all posts without a value in the specified key:
http://pastebin.com/urXJ6sAq
$query = "SELECT DISTINCT meta_value
FROM wp_postmeta
INNER JOIN wp_postmeta m2 ON ( wp_postmeta.post_id = m2.post_id )
WHERE `meta_key` = 'app_type_style'
AND ( `m2.meta_key` = 'numb_buld' AND `m2.meta_value` = '2' )";
I want to get the meta_value in postmeta where (in the same post meta) meta_key=numb_buld and meta_value='2'(for the meta key numb_buld . And the meta_value I want to retreive is for the meta_key=app_style.
Any Help pleas!
If search an integer value in postmeta, modify your query: