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;
";
?>

Published on Dec. 9, 2010 at 1:47 a.m. by Nicolas and tagged WordPress theme, WordPress plugin, SQL, meta data, WordPress. You can follow the discussion with the comment feed for this post.

17 comments

Ostheimer wrote this comment on Dec. 16, 2010, 12:42 p.m.

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...

Reply to this comment
- nicolas wrote this comment on Dec. 16, 2010, 3:07 p.m.
  
  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 :-)
  
  Reply to this comment
Gianko wrote this comment on Dec. 18, 2010, 8:05 a.m.

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.

Reply to this comment
- nicolas wrote this comment on Dec. 18, 2010, 2:16 p.m.
  
  Oh well, sql makes my head spin. I'm not even sure what you're trying to do. :-)
  
  Reply to this comment
  - Gianko wrote this comment on Dec. 18, 2010, 4:52 p.m.
    
    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 :)
    
    Reply to this comment
henri wrote this comment on Jan. 19, 2011, 12:11 a.m.

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!

Reply to this comment
Ken Buck wrote this comment on Jan. 19, 2011, 6:21 a.m.

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!

Reply to this comment
Leo Plaw wrote this comment on March 6, 2011, 9:27 p.m.

Thank you very much for your very helpful post.

Reply to this comment
Time I$ Money wrote this comment on March 11, 2011, 4:57 p.m.

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; }

Reply to this comment
Nicolas Kuttler wrote this comment on April 28, 2011, 10:02 p.m.

By the way, I think that the <a href="http://codex.wordpress.org/Function_Reference/WP_Query#Custom_Field_Parameters">new meta_query</a> in WordPress 3.1 could be able to do this without writing any SQL.

Reply to this comment
Alexey wrote this comment on July 29, 2011, 10:54 p.m.

Thank you Nicolas!
Your code helped me very much.

Reply to this comment
Nick wrote this comment on Dec. 1, 2011, 6:43 a.m.

Thanks for all your hard work. I've been trying for some time to query WP pages by a custom field that I have added to them. I've tried several different types of methods, and never get any results returned.

Have you ever had an issue with a query not returning any results for items that exist in the database?

Thanks.

Reply to this comment
- Nicolas Kuttler wrote this comment on Dec. 1, 2011, 10:45 a.m.
  
  Well, if you get no results the query you built wasn't right. With mysql it's also worth a shot to restart the database.
  
  Reply to this comment
pankaj25 wrote this comment on Dec. 26, 2011, 10:26 p.m.

Hi i am new to WordPress. I need a ads to be populated of larger date(i have two date column one is post_date and other is meta custom field post date) i m using something like below can u please help me out on this Boost_ad is custom date field
"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"

Reply to this comment
Profesor Yeow wrote this comment on Feb. 8, 2012, 2:06 a.m.

Excellent!!! Thanks so much for the code!

Reply to this comment
Diana wrote this comment on Feb. 23, 2012, 6:23 p.m.

Hi there,

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

Reply to this comment