Andy Holmes
Andy Holmes

Reputation: 8077

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.

Upvotes: 3

Views: 127

Answers (3)

Marc B
Marc B

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

Andy Holmes
Andy Holmes

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

Cameron Hurd
Cameron Hurd

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

Related Questions