使用IN获取基于多个元键/值对的WP帖子 [英] Get WP posts based on multiple meta key/value pairs using IN

问题描述

我在格式化查询以基于两个元值的值从数据库中获取帖子列表时遇到一些麻烦.我使用的是WordPress 4.9.4，我需要使用$wpdb->get_results()和不是带有参数的常规get_posts()函数调用.

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.

这是我到目前为止的内容，但是如果我尝试在MySQL查询中包括IN部分，它不会返回任何内容，但应该至少返回一个帖子.

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:

• 帖子在wp_posts中的post_type在book
中
• 在wp_postmeta中具有book_genre的meta_key和fiction的meta_value
• 在wp_postmeta中具有category的meta_key和239, 440的meta_value
• 将结果限制为xyz
• 抵消xyz
的结果

• 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

但是，如果我将var_dump( $posts );放到页面上，上面的查询返回的值是null，所以我想这可能与第一条元键/值规则产生某种冲突?

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?

如果我删除查询的IN部分，它将得到所有预期结果，减去我想要的当然类别的过滤.类别ID是一个ID数组，可以使用PHP的implode()将该ID转换为逗号分隔的字符串.

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

例如:

• Post 1的post_type为book，元密钥/值对为book_genre和fiction，元密钥/值对为"category"和440使用上面的查询 .

• 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的post_type为book，元键/值对为book_genre和fiction，元键/值对为'category'和323，因此使用上述查询不应包含 .

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.

推荐答案

您需要两次连接post_meta表.这是一些数据库理论.

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

当您联接表时，理论上，临时表包含第一个表中的所有项目以及第二个表中的所有项目，并对其进行过滤.因此，例如，如果您每个帖子只有1个元项目，而您有3个帖子，那么您就有

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

和

+-------+----------+----------+------------+
|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          |
+---------+------------+----------+----------+-----------+-------------+

然后，您说:WHERE p.id = pm.post_id

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          |
+---------+------------+----------+----------+-----------+-------------+

因此，每个帖子+元值只有一行.您的查询要查询的行同时具有meta_key = category和meta_key = book_genre`都不存在.

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.

因此，您需要一个将TWICE中的postmeta表联接的表.

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

在这里，您有两个别名为pm1和pm2的postmeta表的合并副本(因为在查询中不能同时将它们称为wp_postmeta.

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.

然后您可以要求:

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.

如果您要走那条路线，我还认为您可以使用WP_Query做到这一点.

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

这篇关于使用IN获取基于多个元键/值对的WP帖子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！