Get post with multiple meta keys and value

I am trying to fetch a post based on the following meta keys.

• post_code with 432C
• location with XYZ
  Both belong to a CPT. I’m trying to fetch the Post with both these meta_values.

I Don’t Want an OR relation, I want a AND relation, I have tried several WP_Query objects and still haven’t found a solution after hours of looking.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This should do it. The default relation is AND so that won’t need to be specified.

$args = array(
    'post_type'  => 'wpse_cpt',
    'meta_query' => array(
        array(
            'key'     => 'post_code',
            'value'   => '432C',
        ),
        array(
            'key'     => 'location',
            'value'   => 'XYZ',
        ),
    ),
);
$query = new WP_Query( $args );

Method 2

$args = array(
    'post_type'  => 'wpse_cpt',
    'meta_query' => array(
        'relation' => 'AND' //**** Use AND or OR as per your required Where Clause
        array(
            'key'     => 'post_code',
            'value'   => '432C',
        ),
        array(
            'key'     => 'location',
            'value'   => 'XYZ',
        ),
    ),
);
$query = new WP_Query( $args );

Method 3

Edit Again: Ah, I didn’t answer the question. You would select a.* to get all columns of the posts with the matching key values, instead of selecting c.meta_value.

The below sql/$wpdb query will select all meta_value%s of a post with post_type of $postType and a meta_key value of $metaKey. Therefore selecting all the different values of a given meta_key (via ‘$metaKey’). The term_relationships table is wordpress’ helper table for relationship connections in tables. wp_posts is the wordpress ‘posts’ table, and, wp_postmeta is the wordpress ‘postmeta’ table. (Note: If you are using custom tables these table names would differ.)

~ Edited to add ‘doing’ notes, requested by @MikeNGarrett

/* $wpdb is a global var provided by the wordpress 'engine'
** for query'ing wordpress database tables. 
*/
global $wpdb; 
$postType = 'mycustomposttype';
$metaKey = 'mymetakey';
$query = "
    SELECT c.meta_value 
    FROM wp_posts a 
    LEFT JOIN wp_term_relationships b 
        ON (a.ID = b.object_id) 
    LEFT JOIN wp_postmeta c 
        ON (a.ID = c.post_id) 
    WHERE a.post_type = %s AND c.meta_key = %s";

$metaValues = $wpdb->prepare(query, [$postType, $metaKey]);
$metaValues = $wpdb->get_results($metaValues);

Notes: I am just reusing the $metaValues variable. There is no other reason to write the results of $metaValues prepare() back to the $metaValues variable. You do however have to pass $metaValues to get_resluts().

Method 4

SOLUTION

The Solution Accepted below worked, however, I wanted to know how is it working?

This is how it’s working

SELECT * FROM wp_posts p, wp_postmeta m1, wp_postmeta m2
    WHERE p.ID = m1.post_id and p.ID = m2.post_id
    AND m1.meta_key = 'key1' AND m1.meta_value = 'value1'
    AND m2.meta_key = 'key2' AND m2.meta_value =  'value2'
    AND p.post_type = 'cpt' AND p.post_status = 'published'

Method 5

I tried using the WP_Query solution suggested above, but got an empty result. The one suggested by Sheraz Ahmed works fine. Here’s my own take for what it’s worth:

//database
wp_posts
+---------+--------------------+--------------+
|  ID     | post_title         | post_type    |
+---------+--------------------+--------------+
| 8567    | Intro to MySQL     | talk         |
+---------+--------------------+--------------+
| 8590    | Intro to PHP       | talk         |
+---------+--------------------+--------------+

wp_postmeta
+---------------+------------+----------------+
|  post_id      | meta_key   | meta_value     |
+---------------+------------+----------------+
| 8567          | speaker    | John Doe       |
+---------------+------------+----------------+
| 8567          | year_of    | 2021           |
+---------------+------------+----------------+
| 8590          | speaker    | John Doe       |
+---------------+------------+----------------+
| 8590          | year_of    | 2021           |
+---------------+------------+----------------+


//search by speaker and Year
$speaker = "John Doe";
$year_of = "2021";

$talks = $wpdb->get_results(  
     $wpdb->prepare( 
         'SELECT post_title, meta_value AS speaker 
         FROM '.$wpdb->prefix.'posts AS pt, '.$wpdb->prefix.'postmeta AS pm 
         WHERE post_type = %s AND pm.post_id = pt.id AND ( meta_key = %s AND meta_value = %s ) 
         AND post_id IN ( SELECT post_id FROM nm_postmeta WHERE meta_key = %s AND meta_value = %s )',
         array( 'talk', 
                'speaker', 
                $speaker, 
                'year_of', 
                $year_of )
    )
);
                
foreach ( $talks as $talk ) {
    echo "<p>".$talk->speaker.': '.$talk_homily->post_title."</p>";
}

John Doe: Intro to MySQL

John Doe: Intro to PHP

Method 6

Tried below object parametes

array(
            'key' => 'post_code',
            'value' =>'432C',
            'compare' => '='
        ),
        array(
            'relation' =>'AND',
             array(

            'key' => 'location',
            'value' => 'XYZ',
             'compare' => '=',

          ),
        )

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0