CODEWITHSUNDEEP
mysqlwordpress
user7236046
user7236046

Reputation:

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

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:

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:

Upvotes: 3

Views: 3953

Answers (1)

magicroundabout
magicroundabout

Reputation: 338

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.

Upvotes: 5

Related Questions