如何避免WordPress get_posts()中自定义字段(元值)查询的AND条件数量限制? [英] How to avoid the limit on the number of AND conditions for custom field (meta value) queries in WordPress get_posts()?
问题描述
我正在为建立在 WordPress 上的网站构建一个搜索工具，似乎 WordPress 中的 get_posts() 函数将我限制为最多 6 个 AND 子句对于元值查询，但我需要该工具来处理 6 个以上的 AND 子句.
I am building a search tool for a website built on WordPress and it seems like the get_posts() function in WordPress is limiting me to a maximum of 6 AND clauses for the meta value query, but I need the tool to take much more than 6 AND clauses.
下面是我的查询的示例，它返回一个空数组.下面的代码 1 是我传递给 get_posts() 的数组的 print_r()，它返回一个空数组.下面的代码 2 与代码 1 相同，但我删除了一个 AND 语句并返回一个结果.注意我尝试了多种组合，这只是一个例子.
Below is an example of my query that is returning an empty array. Code 1 below is the print_r() of the array I am passing to get_posts() that returns an empty array. Code 2 below is identical to code 1 but I removed one AND statement and it returns a result. Note I have tried multiple combinations this is just one example.
为免生疑问，我还在数据库中包含了一个帖子示例(如下面的代码 3)，它应该与代码 1 匹配，因此我认为我们可以排除查询不匹配任何内容的事实.这让我相信 get_posts() 在元查询中最多只能处理 6 个 AND 子句.或者也许我错过了什么?我可以强制 get_posts() 使用 6 个以上的 AND 子句吗?
For avoidance of doubt I have also included an example of a post in the database (as Code 3 below) that should match code 1 so I think we can rule out the fact that the query is not matching anything. This leads me to believe that get_posts() can only handle up to 6 AND clauses in the meta query. Or maybe I am missing something? Can I force get_posts() to work with more than 6 AND clauses?
代码 1:
Array
(
[post_type] => page
[posts_per_page] => 10000
[category_name] => active-cards
[meta_query] => Array
(
[relation] => AND
[0] => Array
(
[key] => card_purchase_rate
[value] => Array
(
[0] => 0
[1] => 46
)
[compare] => BETWEEN
[type] => NUMERIC
)
[1] => Array
(
[key] => card_balance_transfer_rate
[value] => Array
(
[0] => 0
[1] => 14
)
[compare] => BETWEEN
[type] => NUMERIC
)
[2] => Array
(
[relation] => AND
[0] => Array
(
[key] => is_rewards_card
[value] => yes
[compare] => LIKE
)
[1] => Array
(
[key] => is_air_points_card
[value] => yes
[compare] => LIKE
)
[2] => Array
(
[key] => is_frequent_flyer_card
[value] => yes
[compare] => LIKE
)
)
[3] => Array
(
[key] => card_brand
[value] => Array
(
[0] => amex
[1] => mastercard
[2] => visa
)
[compare] => IN
[type] => CHAR
)
[4] => Array
(
[key] => card_provider
[value] => Array
(
[0] => anz
[1] => asb
[2] => bnz
[3] => westpac
[4] => kiwibank
[5] => tsb
[6] => warehouse_money
)
[compare] => IN
[type] => CHAR
)
)
)
代码 2:
Array
(
[post_type] => page
[posts_per_page] => 10000
[category_name] => active-cards
[meta_query] => Array
(
[relation] => AND
[0] => Array
(
[key] => card_purchase_rate
[value] => Array
(
[0] => 0
[1] => 46
)
[compare] => BETWEEN
[type] => NUMERIC
)
[1] => Array
(
[key] => card_balance_transfer_rate
[value] => Array
(
[0] => 0
[1] => 14
)
[compare] => BETWEEN
[type] => NUMERIC
)
[2] => Array
(
[relation] => AND
[0] => Array
(
[key] => is_rewards_card
[value] => yes
[compare] => LIKE
)
[1] => Array
(
[key] => is_air_points_card
[value] => yes
[compare] => LIKE
)
[2] => Array
(
[key] => is_frequent_flyer_card
[value] => yes
[compare] => LIKE
)
)
[3] => Array
(
[key] => card_brand
[value] => Array
(
[0] => amex
[1] => mastercard
[2] => visa
)
[compare] => IN
[type] => CHAR
)
)
)
代码 3(这是代码 2 返回的帖子的示例.代码 1 也应该匹配此项目，但它什么都不返回.
Code 3 (this is an example of the post returned by code 2. Code 1 should also match this item but it returns nothing.
Array
(
[0] => WP_Post Object
(
[ID] => 3742
[post_author] => 1
[post_date] => 2017-01-17 20:01:36
[post_date_gmt] => 2017-01-17 09:01:36
[post_title] => Westpac Airpoints Business MasterCard Credit Card
[post_excerpt] =>
[post_status] => publish
[comment_status] => closed
[ping_status] => closed
[post_password] =>
[post_name] => westpac-airpoints-business-mastercard-credit-card
[to_ping] =>
[pinged] =>
[post_modified] => 2017-04-11 22:08:20
[post_modified_gmt] => 2017-04-11 11:08:20
[post_content_filtered] =>
[post_parent] => 0
[guid] => http://www.creditcardscompare.co.nz/?page_id=3742
[menu_order] => 0
[post_type] => page
[post_mime_type] =>
[comment_count] => 0
[filter] => raw
[meta] => Array
(
[card_annual_fee] => Array
(
[0] => 145
)
[card_purchase_rate] => Array
(
[0] => 20.95
)
[card_balance_transfer_rate] => Array
(
[0] => 1.99
)
[card_cash_rate] => Array
(
[0] => 22.95
)
[card_interest_free_days] => Array
(
[0] => 44
)
[card_provider] => Array
(
[0] => Westpac
)
[card_brand] => Array
(
[0] => Mastercard
)
[is_rewards_card] => Array
(
[0] => a:1:{i:0;s:3:"yes";}
)
[is_low_interest_card] => Array
(
[0] =>
)
[is_no_annual_fee_card] => Array
(
[0] =>
)
[is_balance_transfer_card] => Array
(
[0] =>
)
[is_student_card] => Array
(
[0] =>
)
[is_prestige_card] => Array
(
[0] =>
)
[is_air_new_zealand_card] => Array
(
[0] =>
)
[is_air_points_card] => Array
(
[0] => a:1:{i:0;s:3:"yes";}
)
[is_frequent_flyer_card] => Array
(
[0] => a:1:{i:0;s:3:"yes";}
)
[is_airpoint_lounge_access_card] => Array
(
[0] =>
)
[is_best_card] => Array
(
[0] =>
)
[is_black_card] => Array
(
[0] =>
)
[is_business_card] => Array
(
[0] => a:1:{i:0;s:3:"yes";}
)
[is_cash_advance_card] => Array
(
[0] =>
)
[is_cash_back_card] => Array
(
[0] =>
)
[is_cheap_card] => Array
(
[0] =>
)
[is_debit_card] => Array
(
[0] =>
)
[is_flybuys_card] => Array
(
[0] =>
)
[is_gold_card] => Array
(
[0] =>
)
[is_high_income_card] => Array
(
[0] =>
)
[is_home_loan_card] => Array
(
[0] =>
)
[is_instant_approval_card] => Array
(
[0] =>
)
[is_long_term_balance_transfer_card] => Array
(
[0] =>
)
[is_low_annual_fee_card] => Array
(
[0] =>
)
[is_low_income_card] => Array
(
[0] =>
)
[is_no_foreign_transaction_fee_card] => Array
(
[0] =>
)
[is_platinum_card?] => Array
(
[0] =>
)
[is_purchase_protection_card] => Array
(
[0] =>
)
[is_qantas_card] => Array
(
[0] =>
)
[is_signup_bonus_card] => Array
(
[0] =>
)
[is_travel_insurance_card] => Array
(
[0] =>
)
[is_zero_balance_transfer_card] => Array
(
[0] =>
)
[is_zero_interest_card] => Array
(
[0] =>
)
[card_star_rating] => Array
(
[0] => 4
)
)
[post_url] => http://www.creditcardscompare.co.nz/westpac-airpoints-business-mastercard-credit-card/
)
这里要求的是 WP_query SQL
As requested by here is the WP_query SQL
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id ) INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) INNER JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id ) INNER JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id ) INNER JOIN wp_postmeta AS mt6 ON ( wp_posts.ID = mt6.post_id ) WHERE 1=1 AND (
wp_term_relationships.term_taxonomy_id IN (78)
) AND (
( wp_postmeta.meta_key = 'card_purchase_rate' AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '0' AND '30' )
AND
( mt1.meta_key = 'card_balance_transfer_rate' AND CAST(mt1.meta_value AS SIGNED) BETWEEN '0' AND '13' )
AND
(
( mt2.meta_key = 'is_rewards_card' AND mt2.meta_value LIKE '%yes%' )
AND
( mt3.meta_key = 'is_air_points_card' AND mt3.meta_value LIKE '%yes%' )
AND
( mt4.meta_key = 'is_frequent_flyer_card' AND mt4.meta_value LIKE '%yes%' )
)
AND
( mt5.meta_key = 'card_brand' AND mt5.meta_value IN ('amex','mastercard','visa') )
AND
( mt6.meta_key = 'card_provider' AND mt6.meta_value IN ('anz','asb','bnz','westpac','kiwibank','tsb','warehouse_money') )
) AND wp_posts.post_type = 'page' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10000
推荐答案
我在这里也遇到了元查询限制...
I ran into the meta query limitation as well here...
我希望我能支持你的问题，因为限制没有得到很好的记录.感谢您的贡献.
I wish I could upvote your question because the limitation isn't well-documented. Thank you for your contribution.
你的问题让我意识到问题在于 WP_Query 限制了单个 WP_Query 中元查询子句的数量.
Your question made me realize the problem was that WP_Query limited the number of meta query clauses in a single WP_Query.
编写自己的 SQL 查询是一个很好的解决方案.我决定在应用层使用手动过滤，因为它是一个不依赖于性能的后端功能.
Writing your own SQL query is a good solution. I decided to use manual filtering at the application level since it is a back-end function not dependent on performance.
解决方案:
似乎有 5 个元查询的限制，尽管将帖子状态设置为草稿"似乎允许 6 个元查询，这更不寻常，并且可能与 WP_Query 的某种冲突/副作用有关.
There seems to be a limit of 5 meta queries, though setting the post status to 'draft' seemed to allow 6 meta queries, which is even more unusual and likely related to some kind of conflict/side-effect of WP_Query.
我能够删除其中一个元查询子句并手动过滤结果.
I was able to remove one of the meta query clauses and manually filter the results.
手动过滤子句的选择应取决于哪个子句适用于最小的数据集.
The choice of clauses to manually filter should be dependent on what clause would work with the smallest data set.
这篇关于如何避免WordPress get_posts()中自定义字段(元值)查询的AND条件数量限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
