Get WP posts based on multiple meta key/value pairs using IN

Question

I'm having some trouble formatting my query to fetch a list of posts from the database based on the value of two meta values. I'm using WordPress 4.9.4 and I need to use $wpdb->get_results() and not a normal get_posts() function call with arguments.

Here is what I have so far, but if I try and include the IN part in the MySQL query, it doesn't return anything but it should return at least one post.

global $wpdb;

$posts = $wpdb->get_results(
    $wpdb->prepare(
        'SELECT ' . $wpdb->prefix . 'posts.*, ' . $wpdb->prefix . 'postmeta.* FROM ' . $wpdb->prefix . 'posts, ' . $wpdb->prefix . 'postmeta WHERE ' . $wpdb->prefix . 'posts.post_type = %s AND ' . $wpdb->prefix . 'postmeta.post_id = ' . $wpdb->prefix . 'posts.ID AND (' . $wpdb->prefix . 'postmeta.meta_key = %s AND ' . $wpdb->prefix . 'postmeta.meta_value = %s) AND (' . $wpdb->prefix . 'postmeta.meta_key = %s AND ' . $wpdb->prefix . 'postmeta.meta_value IN (%s)) ORDER BY ' . $wpdb->prefix . 'posts.post_title ASC LIMIT %d OFFSET %d',
        'book',
        'book_genre',
        $genre,
        'category',
        '239, 440',
        $limit,
        $offset
    )
);

The above query should get all posts where:

the post has a post_type of book in wp_posts
has a meta_key of book_genre and meta_value of fiction in wp_postmeta
has a meta_key of category and meta_value of 239, 440 in wp_postmeta
limit results to xyz
offset results of xyz

However, the above query returns a value of null if I var_dump( $posts ); onto the page, so I imagine that the in part is causing some sort of conflict with the first meta key/value rule, perhaps?

If I remove the IN part of the query it gets everything as expected minus the filtering of the categories of course that I'd like. The category ids are an array of ids that are converted into a comma-separated string using PHP's implode().

For example:

Post 1 has a post_type of book, has a meta key/value pair of book_genre and fiction and has a meta key/value pair of 'category' and 440 so should be included using the above query.
Post 2 has a post_type of book, has a meta key/value pair of book_genre and fiction and has a meta key/value pair of 'category' and 323 so should not be included using the above query.

It's not clearer and no one should have to decipher this. If you need help with your query, then just give us a raw MySQL query, and sample input/output data. If your query already works, and you need PHP help, then tell us this. — Tim Biegeleisen
When I say query, I mean $wpdb query which is turned into a MySQL query through the WordPress query method. I understand it's a tricky question to ask and answer, but it's one I've not found anything like before, either it's unique or I might have phrased it differently. The gist is how do I write it in a way that selects posts based on two meta key/value pairs. It seems it's written incorrectly to support two. — user7236046

magicroundabout magicroundabout · Accepted Answer · 2018-03-27T10:35:23

You need to join the post_meta table twice. Here's some database theory.

When you join tables, in theory a temporary table contains all of the items from the first table combined with all of the items from the second table is created and filtered. So if, for example, you have just 1 meta item per post, and you have 3 posts then you have

+-------+----------+
|post_id|post_title|
+-------+----------+
|   1   | 'Post 1' |
|   2   | 'Post 2' |
|   3   | 'Post 3' |
+-------+----------+

and

+-------+----------+----------+------------+
|meta_id| post_id  | meta_key | meta_value |
+-------+----------+----------+------------+
|   10  | 1        |  k1      | v1         |
|   11  | 2        |  k1      | v2         |
|   12  | 3        |  k1      | v3         |
+-------+----------+----------+------------+

And the "theoretical" temporary joined table is:

+---------+------------+----------+----------+-----------+-------------+
|p.post_id|p.post_title|pm.meta_id|pm.post_id|pm.meta_key|pm.meta_value|
+---------+------------+----------+----------+-----------+-------------+
|   1     | 'Post 1'   |   10     | 1        |  k1       | v1          |
|   1     | 'Post 1'   |   11     | 2        |  k1       | v2          |
|   1     | 'Post 1'   |   12     | 3        |  k1       | v3          |
|   2     | 'Post 2'   |   10     | 1        |  k1       | v1          |
|   2     | 'Post 2'   |   11     | 2        |  k1       | v2          |
|   2     | 'Post 2'   |   12     | 3        |  k1       | v3          |
|   3     | 'Post 3'   |   10     | 1        |  k1       | v1          |
|   3     | 'Post 3'   |   11     | 2        |  k1       | v2          |
|   3     | 'Post 3'   |   12     | 3        |  k1       | v3          |
+---------+------------+----------+----------+-----------+-------------+

You then say: WHERE p.id = pm.post_id

and this filters the temporary table to be:

+---------+------------+----------+----------+-----------+-------------+
|p.post_id|p.post_title|pm.meta_id|pm.post_id|pm.meta_key|pm.meta_value|
+---------+------------+----------+----------+-----------+-------------+
|   1     | 'Post 1'   |   10     | 1        |  k1       | v1          |
|   2     | 'Post 2'   |   11     | 2        |  k1       | v2          |
|   3     | 'Post 3'   |   12     | 3        |  k1       | v3          |
+---------+------------+----------+----------+-----------+-------------+

So you only have one row for each post + meta value. Your query is asking for rows that have both meta_key = category and meta_key = book_genre` which don't exist.

So you need a table that joins the postmeta table in TWICE.

You can do this by aliasing the table as you join them. Forgive me for simplifying:

SELECT wp_posts.*, pm1.*, pm2.*
FROM
  wp_posts
  wp_postmeta as pm1
  wp_postmeta as pm2
WHERE pm1.post_id = wp_posts.ID
  AND pm2.post_id = wp_posts.ID
  AND ...etc

Here you have two joined copies of the postmeta table aliased to pm1 and pm2 (as they can't BOTH be called wp_postmeta in the query.

You can then ask for:

AND pm1.meta_key = 'category'
AND pm1.meta_value = X
AND pm2.meta_key = 'book_genre'
AND pm2.meta_key IN (123,456)

Hopefully you can stitch together the rest from that.

I also think you can do this with WP_Query if you want to go that route.

Get WP posts based on multiple meta key/value pairs using IN

1 Answers