php - 使用 `BETWEEN 0 and 100000` 时 Wordpress 不会选择行

Question

我有一个 Wordpress 安装，其中列出了待售特性。我正在尝试查找 2 个值之间的属性。

我遇到的问题是当我做类似的事情时:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = 'type_of_property' AND wp_postmeta.meta_value IN ('semidetached') ) 
  AND 
  ( mt1.meta_key = 'property_value' AND mt1.meta_value BETWEEN '0' AND '100000' )
) AND wp_posts.post_type = 'shared_ownership' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

它只返回 3 行。但是，如果我将其更改为这样的内容:

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )  INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )  INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )  INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id ) WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = 'type_of_property' AND wp_postmeta.meta_value IN ('semidetached') ) 
  AND 
  ( mt1.meta_key = 'property_value' AND mt1.meta_value BETWEEN '0' AND '99999' )
) AND wp_posts.post_type = 'shared_ownership' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

它按预期返回 13 行。这里唯一的区别是最大数量，我不太明白为什么 99999 和 100000 从数据库中得到的结果有这么大的差异。

wordpress 只是忽略多余的零吗？它不认为它是一个数字吗？我不确定如何解决这个问题。

编辑:

下面是我如何使用高级自定义字段等构建此查询的示例。

$args = array(
        'post_type' => $propertyType[0],
        'paged'=>$paged,
        'meta_query' => array(
            'relation' => 'AND',
            array(
                'key' => 'type_of_property',
                'value' => $data[0],
            ),
            array(
                'key' => $propertyValue,
                'value' => array($data[3], $data[4]),
                'compare' => 'BETWEEN'
            ),
            array(
                'key' => 'number_of_bedrooms',
                'value' => $data[2],
                'compare' => 'IN'
            ),
            array(
                'key' => 'town_index',
                'value' => $data[1],
                'compare' => 'IN'
            ),
        )
    );
}
$loop = new WP_Query( $args );

$data 是一个数组。 $data[3] 和 $data[4] 等于我上面提到的值，只是从表单 POST 中填充。

Answer 1

尝试删除单引号。我相信 SQL 不确定如何“键入”这些值以进行比较。 (字符串与标量，正如 @danfromgermany 在评论中指出的那样。)

要测试您的“额外零”假设，这将证明这一点:尝试将 10000 值更改为 100001。正如您所怀疑的那样，SQL 可能能够更好地解释没有右填充 0 的查询。

---

编辑:要对您的查询进行细粒度控制以避免 WP 应用单引号，您可以只使用 $wpdb 对象。

来自 https://codex.wordpress.org/Class_Reference/wpdb :

Using the $wpdb Object

WordPress provides a global object variable, $wpdb, which is an instantiation of the wpdb class defined in /wp-includes/wp-db.php By default, $wpdb is instantiated to talk to the WordPress database. To access $wpdb in your WordPress PHP code, declare $wpdb as a global variable using the global keyword, or use the superglobal $GLOBALS in the following manner:

global $wpdb;
$results = $wpdb->get_results( 'SELECT * FROM wp_options WHERE option_id = 1', OBJECT );

---

编辑#2:

尝试在 $args 数组的 meta_query 部分中将 type 的键/值分配为 NUMERIC :

(摘自 https://codex.wordpress.org/Class_Reference/WP_Meta_Query#Accepted_Arguments )

$args = array(
    // ...
    'meta_query' => array(
        'relation' => 'AND',
        // ...
        array(
            'key' => $propertyValue,
            'value' => array($data[3], $data[4]),
            'type' => 'NUMERIC',
            'compare' => 'BETWEEN'
        ),
        //...
    )
);