Get post with multiple meta keys and value

Question

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.

Answer 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 );

Answer 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 );

Answer 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().

Answer 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'

Answer 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

Answer 6

Tried below object parametes

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

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

          ),
        )