$Wpdb post meta query is not working with mutliple meta keys and values? [closed]

First, let me point out a few issues.

1. Your SQL statement has 2 different conditions that cannot both be true: meta key = “state” AND meta_key LIKE “habitat_types_%_habitat_type”. To do this properly, post meta must be inner joined onto itself (see more about this below).

2. You are not inner joining the posts table, meaning you might be grabbing orphaned post meta (post meta that doesn’t even belong to a post). Also, you have no way of filtering by post type or post status. At minimum, you need to inner join post_meta onto itself to get the results you want, but below i’ve inner joined post_meta onto posts, and then post_meta onto that once more.

3. If it weren’t for your wildcard meta key “habitat_types_%_habitat_type”, you could use a get_posts() or new WP_Query (which work more or less the same) to get the same thing but in a much simpler way.

Can you provide more info on the “habitat_types_%_habitat_type” meta key? Why are we using a wildcard here, and how many different possibilities are there?

Here is an SQL solution:

global $wpdb;
$query = '';
$vars = array();

// select statement gets a bit complicated
$query .= 'SELECT *, pm1.meta_key AS mk1, pm1.meta_value AS mv1, pm2.meta_key AS mk2, pm2.meta_value AS mv2 '; // each line has a space at the end
$query .= 'FROM ' . $wpdb->posts . ' AS posts ';
$query .= 'INNER JOIN ' . $wpdb->postmeta . ' AS pm1 ON posts.ID = pm1.post_id ';
$query .= 'INNER JOIN ' . $wpdb->postmeta . ' AS pm2 ON posts.ID = pm2.post_id ';

// placeholder 'where' statement, so all other ones can use 'and'
$query .= 'WHERE 1 = 1 ';

// %s and %d get replaced by elements in the $vars array, the number of %s/%d's should be the same 
// as the length of the array. Its only meant to be used when the $vars come from user input or maybe a variable.
// but here i'm just using them for hardcoded values anyways.
$query .= 'AND posts.post_status = %s ';
$vars[] = 'publish';

$query .= 'AND posts.post_type = %s ';
$vars[] = 'post'; // insert your own post type here

// first post meta join
$query .= 'AND pm1.meta_key LIKE "habitat_types_%_habitat_type" ';    
$query .= 'AND pm1.meta_value = %s ';
$vars[] = 'Coastal Sage Scrub';

// second post meta join
$query .= 'AND pm2.meta_key LIKE "state" ';
$query .= 'AND pm2.meta_value = %s ';
$vars[] = 'Oregon';

$query .= 'GROUP BY posts.ID ';
$query .= 'ORDER BY posts.post_date ';
$query .= ' ;'; // semi-colon needed or query will be invalid

// note: if using $wpdb->prepare where $vars is empty, error will be thrown.
$r = $wpdb->get_results( $wpdb->prepare( $query, $vars ) );

if ( $r ) {
    foreach ( $r as $dbRow ) {
        echo $dbRow->ID; // should be post ID
        echo $dbRow->mv1; // this should be Coastal Sage Scrub
        echo $dbRow->mv2; // should be Oregon
    }
}

If it weren’t for the wildcard in the “habitat_types_%_habitat_type” meta key, then a much simpler solution would be as follows (see the comments in the code below)

$args = array(
    'post_type' => 'post', // insert your own post type here
    'post_status' => 'publish', // so we dont get posts from the trash bin
    'posts_per_page' => -1,
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key' => 'state',
            'value' => 'Oregon',
            'compare' => '=',
        ),
        // I dont think this will work:
//        array(
//            'key' => 'habitat_types_%_habitat_type',
//            'value' => 'Oregon',
//            'compare' => '=',
//        ),
        // this would work, but its probably not what you want
        array(
            'key' => 'habitat_type',
            'value' => 'Coastal Sage Scrub',
            'compare' => '=',
        ),
    )
);

$posts = get_posts( $args );

if ( $posts ) {
    foreach ( $posts as $p ) {
        echo $p->ID; // post ID
        $state = get_post_meta( $p->ID, 'state', true );
        echo $state; // should be Oregon
        // getting "habitat_types" is a bit tricky because i don't know why there's a wildcard in the meta key
    }
}

// for the sake of understanding how things work, I recommend trying this:
// $wpq = new WP_Query( $args );
// var_dump( $wpq );
// Look for the 'request' index of $wpq (can't remember if its in $wpq->query->request, or just $wpq->request)
// The request will be the SQL statement. Play around with the meta query to see how your SQL changes. You'll notice it looks pretty similar to the SQL statement in the first example.