WordPress自定义查询：如何检索匹配JSON数据的文章？

要解决这个问题，我们需要通过ACF字段中的短代码ID关联到wp_shortcode表，解析其中的JSON数据并筛选符合条件的文章。以下是具体的实现方案：

一、直接SQL查询方案 #

首先，我们需要从wp_postmeta的meta_value中提取短代码的ID，然后关联wp_shortcode表，利用MySQL的JSON函数解析数据并应用筛选条件。

示例查询（符合第一个搜索条件） #

SELECT DISTINCT p.ID, p.post_title
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
JOIN wp_shortcode sc ON REGEXP_REPLACE(pm.meta_value, '.*id="([0-9]+)".*', '$1') = sc.id
WHERE pm.meta_key = '_shortcode'
  AND JSON_UNQUOTE(JSON_EXTRACT(sc.data, '$.bathroom')) = '3'
  AND CAST(JSON_UNQUOTE(JSON_EXTRACT(sc.data, '$.sale_price')) AS DECIMAL(10,0)) BETWEEN 1000 AND 2000000
  AND p.post_type = 'post'
  AND p.post_status = 'publish';

关键步骤说明： #

• 提取短代码ID：使用REGEXP_REPLACE从[shortcode id="1234"]格式的字符串中提取ID值，适配MySQL 8.0+；如果是MySQL 5.7，可以用REGEXP_SUBSTR(meta_value, 'id="([0-9]+)"', 1, 1, 'e')替代。
• 解析JSON数据：JSON_EXTRACT从data字段中取出指定键的值，JSON_UNQUOTE去除字符串引号，CAST将sale_price转为数字类型以便区间比较。
• 去重处理：用DISTINCT确保同一文章即使关联多条符合条件的wp_shortcode记录，也只返回一次。

当搜索条件为sale_price_to=2000时，由于没有匹配的价格数据，该查询会返回空结果，符合预期。

二、WordPress代码实现 #

如果你需要在WordPress主题或插件中集成这个逻辑，可以使用$wpdb类执行查询，或者通过WP_Query的过滤器扩展功能。

方式1：使用$wpdb直接查询 #

global $wpdb;

// 定义搜索参数
$sale_price_from = 1000;
$sale_price_to = 2000000;
$bathroom = 3;

// 准备并执行查询
$query = $wpdb->prepare("
    SELECT DISTINCT p.ID, p.post_title
    FROM {$wpdb->posts} p
    JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
    JOIN wp_shortcode sc ON REGEXP_REPLACE(pm.meta_value, '.*id=\"([0-9]+)\".*', '$1') = sc.id
    WHERE pm.meta_key = '_shortcode'
      AND JSON_UNQUOTE(JSON_EXTRACT(sc.data, '$.bathroom')) = %s
      AND CAST(JSON_UNQUOTE(JSON_EXTRACT(sc.data, '$.sale_price')) AS DECIMAL(10,0)) BETWEEN %d AND %d
      AND p.post_type = 'post'
      AND p.post_status = 'publish';
", $bathroom, $sale_price_from, $sale_price_to);

$results = $wpdb->get_results($query);

// 输出结果
if (!empty($results)) {
    foreach ($results as $post) {
        echo '<h2>' . esc_html($post->post_title) . '</h2>';
    }
} else {
    echo '没有找到符合条件的文章';
}

方式2：集成到WP_Query #

如果你想通过WordPress原生的WP_Query来实现，可以使用过滤器扩展查询逻辑：

// 扩展JOIN子句
function custom_search_join($join) {
    global $wpdb;
    // 根据你的搜索触发条件调整判断逻辑
    if (isset($_GET['custom_property_search'])) {
        $join .= " JOIN {$wpdb->postmeta} pm ON {$wpdb->posts}.ID = pm.post_id JOIN wp_shortcode sc ON REGEXP_REPLACE(pm.meta_value, '.*id=\"([0-9]+)\".*', '$1') = sc.id ";
    }
    return $join;
}
add_filter('posts_join', 'custom_search_join');

// 扩展WHERE子句
function custom_search_where($where) {
    global $wpdb;
    if (isset($_GET['custom_property_search'])) {
        $sale_price_from = intval($_GET['sale_price_from']);
        $sale_price_to = intval($_GET['sale_price_to']);
        $bathroom = sanitize_text_field($_GET['bathroom']);
        
        $where .= $wpdb->prepare("
            AND pm.meta_key = '_shortcode'
            AND JSON_UNQUOTE(JSON_EXTRACT(sc.data, '$.bathroom')) = %s
            AND CAST(JSON_UNQUOTE(JSON_EXTRACT(sc.data, '$.sale_price')) AS DECIMAL(10,0)) BETWEEN %d AND %d
        ", $bathroom, $sale_price_from, $sale_price_to);
    }
    return $where;
}
add_filter('posts_where', 'custom_search_where');

// 使用WP_Query获取结果
$args = array(
    'post_type' => 'post',
    'post_status' => 'publish',
    'posts_per_page' => -1,
);
$property_query = new WP_Query($args);

// 循环输出文章
if ($property_query->have_posts()) {
    while ($property_query->have_posts()) {
        $property_query->the_post();
        the_title('<h2>', '</h2>');
        the_excerpt();
    }
    wp_reset_postdata();
} else {
    echo '没有符合条件的文章';
}

注意事项 #

• 确保你的MySQL版本在5.7及以上，因为需要支持JSON函数。
• 如果短代码ID的格式有变化（比如使用单引号、包含非数字字符），需要调整正则表达式以匹配实际格式。
• 建议给wp_shortcode表的id字段添加索引，提升大数据库下的查询效率。

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