Andy Holmes Andy Holmes - 1 month ago 9
MySQL Question

Wordpress won't select rows when using `BETWEEN 0 and 100000`

I have a Wordpress installation that lists properties for sale. I'm trying to find properties between 2 values.

The problem I have is when I do something like:

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


It only returns 3 rows. However, if I change it to something like this:

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


It returns 13 rows as expected. The only difference here is the maximum number, and I can't quite understand why there is such a huge difference between
99999
and
100000
in terms of results from the database.

Is wordpress just ignoring the extra zeros? Does it not see it as a number? I'm not sure on how to go about fixing this one.

EDIT:

Below is an example of how I'm building this query using advanced custom fields etc.

$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
is an array.
$data[3]
and
$data[4]
are equal to the values I mentioned above, just populated from a form POST.

Answer

Try removing the single quotes. I believe SQL is unsure of how to "type" those values for comparison. (string vs. scalar, as @danfromgermany points out in the comments.)

To test your "extra zeroes" hypothesis, which would prove this: try changing the 10000 value to 100001. As you suspect, SQL might be better able to interpret the query without the right-padded 0s.


Edit: To get that fine-grain control over your query that avoids WP applying single quotes, you might just use the $wpdb object.

From 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 );

Edit #2:

Try assigning a key/value of type as NUMERIC in the meta_query pieces of your $args array:

(Gleaned from from 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'
        ),
        //...
    )
);
Comments