按类别和自定义字段对wordpress过滤器的高级SQL查询

Question

我想选择按类别和其他自定义字段过滤的自定义字段“portata”的不同值。

这是查询，选择按类别自定义值：

global $wpdb; 
$querystr2 = " 
SELECT DISTINCT wpostmeta.meta_value 
FROM $wpdb->posts wposts 
LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id 
LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id) 
LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) 


WHERE wpostmeta.meta_key = 'portata' 
AND $wpdb->term_taxonomy.taxonomy = 'category' 
AND $wpdb->term_taxonomy.term_id IN($variabile_c) 
ORDER BY wpostmeta.meta_value ASC 

"; 

我怎么可以在其他自定义字段添加到过滤器？

Answer 1

您需要再次加入$ wpdb-> postmeta，为每个连接使用一个单独的别名，并向WHERE子句中添加几个AND以应用额外的条件 - 例如：

SELECT DISTINCT wpostmeta1.meta_value 
    FROM $wpdb->posts wposts 
    LEFT JOIN $wpdb->postmeta wpostmeta1 ON wposts.ID = wpostmeta1.post_id 
    LEFT JOIN $wpdb->postmeta wpostmeta2 ON wposts.ID = wpostmeta2.post_id 
    LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id) 
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) 
    WHERE wpostmeta1.meta_key = 'portata' 
    AND wpostmeta2.meta_key = 'other-custom-field-value' 
    AND $wpdb->term_taxonomy.taxonomy = 'category' 
    AND $wpdb->term_taxonomy.term_id IN($variabile_c) 
    ORDER BY wpostmeta1.meta_value ASC 

用您需要过滤的第二个自定义字段值替换“其他自定义字段值”。

Answer 2

谢谢亚伦米勒！现在它的工作！

SELECT DISTINCT wpostmeta1.meta_value 
FROM $wpdb->posts wposts 
    LEFT JOIN $wpdb->postmeta wpostmeta1 ON wposts.ID = wpostmeta1.post_id 
    LEFT JOIN $wpdb->postmeta wpostmeta2 ON wposts.ID = wpostmeta2.post_id 
    LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id) 
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id) 
WHERE wpostmeta1.meta_key = 'portata' 
    AND wpostmeta2.meta_key = 'alimentazione' 
AND wpostmeta2.meta_value = '$variabile_portata' 
    AND $wpdb->term_taxonomy.taxonomy = 'category' 
    AND $wpdb->term_taxonomy.term_id IN($variabile_c) 
ORDER BY wpostmeta1.meta_value ASC 

谢谢Aaron Miller。 现在，如果你想通过自定义字段添加1个其他文件管理器，我必须添加1个左连接吗？ 谢谢！