如何为已有的WP_Query对象添加JOIN子句过滤WooCommerce产品

正确实现方案 #

function my_query( $query, $terms, $limits ) {
    // 将当前目标查询传入过滤器闭包
    add_filter( 'posts_join', function( $join, $current_query ) use ( $query ) {
        // 仅处理目标查询，跳过其他所有查询
        if ( $current_query !== $query ) {
            return $join;
        }
        $join .= " LEFT JOIN view_excludable_attributes AS v ON wp_term_relationships.object_id = v.post_id AND wp_term_relationships.term_taxonomy_id = v.term_taxonomy_id ";
        // 执行完逻辑后立即移除过滤器，避免影响后续查询
        remove_filter( current_filter(), __FUNCTION__ );
        return $join;
    }, 10, 2 );

    add_filter( 'posts_where', function( $where, $current_query ) use ( $query ) {
        if ( $current_query !== $query ) {
            return $where;
        }
        // 拼接WHERE条件前加AND，避免SQL语法错误
        $where .= " AND v.post_id IS NULL ";
        remove_filter( current_filter(), __FUNCTION__ );
        return $where;
    }, 10, 2 );

    return $query;
}
add_filter( 'plugin_query', 'my_query', 10, 3 );

核心逻辑说明 #

1. 不需要给全站注册永久过滤器，只需要在拿到目标查询的这个时机临时注册过滤器即可，逻辑执行完会自动移除，不会影响其他页面、其他场景的查询。
2. 通过use语法将当前拿到的$query传入闭包，和过滤器触发时传入的$current_query做全等判断，确保只有你要修改的这个查询会被调整。
3. posts_join和posts_where会在WP_Query生成SQL语句时自动触发，不需要手动调用apply_filters执行。

兼容表前缀优化版本 #

如果你的WordPress表前缀不是默认的wp_，可以用以下写法适配自定义表前缀：

function my_query( $query, $terms, $limits ) {
    global $wpdb;
    $term_rel_table = $wpdb->term_relationships;

    add_filter( 'posts_join', function( $join, $current_query ) use ( $query, $term_rel_table ) {
        if ( $current_query !== $query ) {
            return $join;
        }
        $join .= " LEFT JOIN view_excludable_attributes AS v ON {$term_rel_table}.object_id = v.post_id AND {$term_rel_table}.term_taxonomy_id = v.term_taxonomy_id ";
        remove_filter( current_filter(), __FUNCTION__ );
        return $join;
    }, 10, 2 );

    add_filter( 'posts_where', function( $where, $current_query ) use ( $query ) {
        if ( $current_query !== $query ) {
            return $where;
        }
        $where .= " AND v.post_id IS NULL ";
        remove_filter( current_filter(), __FUNCTION__ );
        return $where;
    }, 10, 2 );

    return $query;
}
add_filter( 'plugin_query', 'my_query', 10, 3 );

内容的提问来源于stack exchange，提问作者WoodrowShigeru