Reputation: 8077
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.
Upvotes: 3
Views: 127
Reputation: 360762
Your values are being compared as STRINGS, which means which string comparison rules apply:
'2' > '3' -> false
'2' > '1999999999' -> true
In your case:
'2' BETWEEN '0' and '10000'
executes as
('0' <= '2') && ('2' < '10000')
(true) && (false)
false
and for the '2'<'10...', going char by char:
'2' < '10000'
a abcde
runs as:
('2' < '1') && (null < '0') && (null < '0') etc...
a b c
(false) && (false) && (false) ....
false
If it helps, just convert your numbers into letters:
1234567890
abcdefgjij
'2' BETWEEN '1' AND '100000000'
'B' BETWEEN 'A' AND 'AJJJJJJJJ'
which obviously works out to false
Upvotes: 1
Reputation: 8077
Okay so I tried all the suggested ideas, and they worked fine. However, because we are using Advanced Custom Fields and running a query with the $args
ACF suggests, apparently you can just do something like this:
$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]),
'type' => 'DECIMAL',
'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 );
So defining a type
of DECIMAL
on the arguments, it auto cast the field for me to this excerpt: mt1.meta_key = 'property_value' AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '0' AND '445295' )
Upvotes: 1
Reputation: 5031
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'
),
//...
)
);
Upvotes: 2