mysql - 构建一个 WordPress 查询以按类别以及 meta_key 的数字范围选择帖子

Question

这是一个房地产 WordPress 博客。我正在开发一个链式三选框。

我已经弄清楚了大部分内容，我不知道如何查询帖子以仅返回基于类别和价格范围的卧室数量的元数据。

我可以根据类别进行选择:

    SELECT DISTINCT meta_value
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON 
(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE 1
AND wp_term_taxonomy.term_id IN ($_POST[id])
AND meta_key = '_lwi_bedroomsCount'
GROUP BY wp_posts.ID
ORDER BY meta_value ASC

我需要添加到此查询中的是这些参数的价格范围:

    $args['meta_query'][] = array(
'key'   => '_lwi_price',
'value' => array(strval($pricerange[0]),strval($pricerange[1])),
'compare' => 'BETWEEN',
'type' => 'numeric', );

如何将其添加到我的 SQL 语句中？

注意:$_POST[id] 是类别 ID。

Answer 1

    ### get term id by Category name ####

    $sql = "SELECT * "
           ."   FROM wp_b9la0x2_terms wp_b9la0x2_terms "
           ."   WHERE (wp_b9la0x2_terms.name = '".$_REQUEST['category_name']."');";   

    $result = $conn->query($sql) or die($conn->error);
    $row = $result->fetch_assoc();

    #### get all post by category and also sub category ###

    $sql = "SELECT wp_b9la0x2_posts.ID,"
            . "       wp_b9la0x2_posts.post_title, "
            . "       wp_b9la0x2_posts.post_content, "
            . "       wp_b9la0x2_posts.post_excerpt, "
            . "       wp_b9la0x2_posts.post_date, "
            . "       wp_b9la0x2_posts.post_date_gmt, "
            . "       wp_b9la0x2_posts.post_modified, "
            . "       wp_b9la0x2_posts.post_modified_gmt, "
            . "       wp_b9la0x2_posts.guid, "
            . "       wp_b9la0x2_term_taxonomy.description, "
            . "       wp_b9la0x2_terms.name, "
            . "       wp_b9la0x2_posts.post_author, "
            . "       wp_b9la0x2_users.display_name as author_name "
            . "  FROM wp_b9la0x2_terms "
            . "  LEFT JOIN wp_b9la0x2_term_taxonomy wp_b9la0x2_term_taxonomy "
            ."          ON wp_b9la0x2_terms.term_id = wp_b9la0x2_term_taxonomy.term_id "
            ."   LEFT JOIN wp_b9la0x2_term_relationships wp_b9la0x2_term_relationships "
            ."          ON wp_b9la0x2_term_taxonomy.term_taxonomy_id = wp_b9la0x2_term_relationships.term_taxonomy_id "
            ."   LEFT JOIN wp_b9la0x2_posts wp_b9la0x2_posts"
            ."           ON wp_b9la0x2_term_relationships.object_id = wp_b9la0x2_posts.ID "
            ."   LEFT JOIN wp_b9la0x2_users wp_b9la0x2_users"
            ."           ON wp_b9la0x2_users.ID = wp_b9la0x2_posts.post_author "
            ."  WHERE (wp_b9la0x2_term_taxonomy.term_id = '".$row['term_id']."' OR `parent` = '".$row['term_id']."')"
            ."   AND (wp_b9la0x2_posts.post_status = 'publish')"
            ."   AND (wp_b9la0x2_posts.post_type = 'post')"
            . "  GROUP BY  wp_b9la0x2_posts.ID"
            . "  ORDER BY wp_b9la0x2_posts.post_date DESC";
    $result = $conn->query($sql) or die($conn->error);
    $res = array();

    while($row = $result->fetch_assoc()) {
        $test = array();
        foreach($row as $key => $val){
            $test[$key] = utf8_encode($val);
        }
       $res[] = $test;
    }
    echo json_encode($res);