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. Feeling generous? Donate!

21 comments

  1. avatar
    wrote this comment on

    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
    1. avatar
      wrote this comment on

      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
  2. avatar
    wrote this comment on

    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-&gt;prefix}posts
    INNER JOIN {$wpdb-&gt;prefix}postmeta m1
    ON ( {$wpdb-&gt;prefix}posts.ID = m1.post_id )
    INNER JOIN {$wpdb-&gt;prefix}postmeta m2
    ON ( {$wpdb-&gt;prefix}posts.ID = m2.post_id )
    LEFT JOIN $wpdb-&gt;term_relationships ON (wposts.ID = $wpdb-&gt;term_relationships.object_id)
    LEFT JOIN $wpdb-&gt;term_taxonomy ON ($wpdb-&gt;term_relationships.term_taxonomy_id = $wpdb-&gt;term_taxonomy.term_taxonomy_id)
    WHERE
    {$wpdb-&gt;prefix}posts.post_type = 'post'
    AND {$wpdb-&gt;prefix}posts.post_status = 'publish'
    AND ( m1.meta_key = 'Fecha Inicio' AND m1.meta_value = '".$hoy."' )
    AND $wpdb-&gt;term_taxonomy.taxonomy = 'category'
    AND $wpdb-&gt;term_taxonomy.term_id IN(10)
    GROUP BY {$wpdb-&gt;prefix}posts.ID
    ORDER BY {$wpdb-&gt;prefix}posts.post_date
    DESC;
    ";
    </code>

    lol.. :$ but it didn't work

    thanks in advance.

    Reply to this comment
    1. avatar
      wrote this comment on

      Oh well, sql makes my head spin. I'm not even sure what you're trying to do. :-)

      Reply to this comment
      1. avatar
        wrote this comment on

        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
  3. avatar
    wrote this comment on

    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-&gt;postmeta.meta_value AS BINARY) ASC";

    thanks!

    Reply to this comment
  4. avatar
    wrote this comment on

    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
  5. avatar
    wrote this comment on

    Thank you very much for your very helpful post.

    Reply to this comment
  6. avatar
    wrote this comment on

    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-&gt;postmeta
    WHERE meta_key = 'event_date'
    AND STR_TO_DATE(meta_value,'%m/%d/%Y %H:%i:%s') &gt;= CURDATE() ) AS metasort
    ON $wpdb-&gt;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
  7. avatar
    wrote this comment on
    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
  8. avatar
    wrote this comment on
    Thank you Nicolas!
    Your code helped me very much.
    Reply to this comment
  9. avatar
    wrote this comment on
    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
    1. avatar
      wrote this comment on
      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
  10. avatar
    wrote this comment on
    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
  11. avatar
    wrote this comment on
    Excellent!!! Thanks so much for the code!
    Reply to this comment
  12. avatar
    wrote this comment on
    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
  13. avatar
    wrote this comment on
    Helo everyone I got this code :

    $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!
    Reply to this comment
  14. avatar
    wrote this comment on
    Extremely helpful, thanks so much for posting this! I had almost given up on displaying posts based on more than one custom field.
    Reply to this comment
  15. avatar
    wrote this comment on
    Tks man!

    If search an integer value in postmeta, modify your query:

    <?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 = 'price' AND CAST(m1.meta_value AS DECIMAL) > '100' )
    AND ( m1.meta_key = 'price' AND CAST(m1.meta_value AS DECIMAL) < '5000' )
    AND ( m2.meta_key = 'code' AND m2.meta_value = '1287' )
    GROUP BY {$wpdb->prefix}posts.ID
    ORDER BY {$wpdb->prefix}posts.post_date
    DESC;
    ";
    ?>

    Reply to this comment
    1. avatar
      wrote this comment on
      Cheers! I'm so glad I use an ORM nowadays, and sane db schemes :-D
      Reply to this comment

Start a new thread

Cancel reply
Markdown. Syntax highlighting with <code lang="php"><?php echo "Hello, world!"; ?></code> etc.