虽然这确实是一个MySQL问题，但它确实有助于理解WordPress SQL模式，而且我喜欢尝试优化SQL查询，而不是将您发送到StackOverflow 我会尽量在这里回答你。你可能仍然想把它贴在那里，以获得一些其他的意见。
虽然我不完全理解你的要求，但我想我理解你的要求，我想我是这样做的，我想提出以下内容，看看它是否能更好地满足你的需求。我没有您的数据，因此我很难确定它是否确实有效，但正如我所说，我认为它满足您的需要：
function filter_resources($phase,$wa,$aus,$topics){
global $wpdb;
$sql =<<<SQL
SELECT
t.slug,p.*
FROM
wp_posts p
INNER JOIN wp_term_relationships tr ON p.ID=tr.object_id
INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms t ON tt.term_id = t.term_id
WHERE 1=1
AND p.post_type = \'resources\'
AND p.post_status = \'publish\'
AND t.term_id IN (%d,%d,%d,%d)
AND CONCAT(tt.taxonomy,\'/\',t.term_id) IN (
\'phase-of-learning/%s\',
\'wa-curriculum/%s\',
\'australian-curriculum/%s\',
\'topics/%s\'
)
GROUP BY
p.ID
HAVING
COUNT(*)=4
ORDER BY
p.post_date DESC
SQL;
$sql = $wpdb->prepare($sql,
$phase,$wa,$aus,$topics, // For the %d replacements
$phase,$wa,$aus,$topics // For the %s replacements
);
$results = $wpdb->get_results($sql,OBJECT);
return $results;
}
基本上，这将为您提供应用了所有分类法术语的所有帖子，它通过执行自由形式查询来匹配应用了分类法/术语的所有帖子，但仅限于应用了所有术语的帖子分组
wp_post.ID 并查找加入该职位4次的所有记录。运行MySQL时
EXPLAIN 与您所拥有的相比，优化看起来相当不错；加入的表要少得多。希望这就是你需要的逻辑。
使用瞬态API进行缓存，如果您试图提高性能，也可以考虑在有限的时间内（1小时、4小时、12小时或更长时间）将结果缓存在“瞬态”中Transients API:
- Overview of WordPress’ Transients API
define(\'NUM_HOURS\',4); // Time to cache set for your use case
$data = get_transient( \'your_transient_key\' );
if( !$data ) {
$data = // Do something to get your data here
set_transient( \'your_transient_key\', $data, 60 * 60 * NUM_HOURS );
}
在您的filter_resources() 函数它可能看起来像这样：define(\'RESOURCE_CACHE_HOURS\',4);
function filter_resources($phase,$wa,$aus,$topics){
$resources = get_transient( \'yoursite_filtered_resources\' );
if(!$resources) {
global $wpdb;
$sql =<<<SQL
SELECT
t.slug,p.*
FROM
wp_posts p
INNER JOIN wp_term_relationships tr ON p.ID=tr.object_id
INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms t ON tt.term_id = t.term_id
WHERE 1=1
AND p.post_type = \'resources\'
AND p.post_status = \'publish\'
AND t.term_id IN (%d,%d,%d,%d)
AND CONCAT(tt.taxonomy,\'/\',t.term_id) IN (
\'phase-of-learning/%s\',
\'wa-curriculum/%s\',
\'australian-curriculum/%s\',
\'topics/%s\'
)
GROUP BY
p.ID
HAVING
COUNT(*)=4
ORDER BY
p.post_date DESC
SQL;
$sql = $wpdb->prepare($sql,
$phase,$wa,$aus,$topics, // For the %d replacements
$phase,$wa,$aus,$topics // For the %s replacements
);
$resources = $wpdb->get_results($sql,OBJECT);
$hours = RESOURCE_CACHE_HOURS * 60 * 60;
set_transient( \'yoursite_filtered_resources\', $resources, $hours);
}
return $resources;
}
更新下面是另一个代码，它试图处理用户选择的标准少于四个的情况：define(\'RESOURCE_CACHE_HOURS\',4);
function filter_resources($phase,$wa,$aus,$topics){
$resources = get_transient( \'yoursite_filtered_resources\' );
if(!$resources) {
$terms = $taxterms = array();
if (!empty($phase))
$taxterms[$phase] = \'phase-of-learning/%s\';
if (!empty($wa))
$taxterms[$wa] = \'wa-curriculum/%s\';
if (!empty($aus))
$taxterms[$aus] = \'axustralian-curriculum/%s\';
if (!empty($topics))
$taxterms[$topics] = \'topics/%s\';
$count = count($taxterms);
$having = ($count==0 ? \'\' : "HAVING COUNT(*)={$count}");
$values = array_keys(array_flip($tax_terms));
$values = array_merge($values,$values); // For %d and $s
$taxterms = implode("\',\'",$taxterms);
$terms = implode(\',\',array_fill(0,$count,\'d%\'));
global $wpdb;
$sql =<<<SQL
SELECT
t.slug,p.*
FROM
wp_posts p
INNER JOIN wp_term_relationships tr ON p.ID=tr.object_id
INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms t ON tt.term_id = t.term_id
WHERE 1=1
AND p.post_type = \'resources\'
AND p.post_status = \'publish\'
AND t.term_id IN ({$terms})
AND CONCAT(tt.taxonomy,\'/\',t.term_id) IN (\'{$taxterms}\')
GROUP BY
p.ID
{$having}
ORDER BY
p.post_date DESC
SQL;
$sql = $wpdb->prepare($sql,$values);
$resources = $wpdb->get_results($sql,OBJECT);
$hours = RESOURCE_CACHE_HOURS * 60 * 60;
set_transient( \'yoursite_filtered_resources\', $resources, $hours);
}
return $resources;
}
