WordPress query, meta_query, compare BETWEEN, no results

Question

I have code for my filter. It worked well until I add new product in my database. I found the problem, but dont know what to do with that.

I have parameters "alc_min" and "alc_max" in my filter. I get these from crawling all products. After I send this filter, I fire this code:

$meta_query = array();
$b = "alc_min";
$c = "alc_max";
if (isset ( $data [$b] ) && isset ( $data [$c] )) {
    $compare = "BETWEEN";
    $a = array (
            'key' => "alc",
            'value' => array (
                    $data [$b],
                    $data [$c]
            ),
            'compare' => $compare
    );
    array_push ( $meta_query, $a );
}

            $items = new WP_Query ( array (
                    'post_type' => $type,
                    'posts_per_page' => $posts_per_page,
                    'order' => $order,
                    'meta_key' => $orderkey,
                    'orderby' => $orderby,
                    'post_status' => 'publish',
                    'meta_query' => $meta_query,
                    'paged' => $paged
            ) );

Until now, it worked well. No I add new product with "alc" <10 and I found, that if I have "alc_min" and "alc_max" <10 or >10, it is ok. But if "alc_min" is <10 and "alc_max" >10 I get no results at all.

Does anyone any idea what to check or fix?

Answer 1

After the clarification, I've suspected that the reason why selecting "alc_min" = 7 and "alc_max" = 13 doesn't yield any result is because of the column datatype. Consider this example:

CREATE TABLE table1 (
alc VARCHAR(50));

INSERT INTO table1 VALUES 
('7'),
('9'),
('11'),
('13');

The table above is created with alc column datatype as VARCHAR instead of INTEGER (or numeric datatype). I've tested that running either one of the query below:

SELECT * FROM table1 WHERE alc BETWEEN '7' AND '9';
SELECT * FROM table1 WHERE alc BETWEEN '11' AND '13';

will return the expected result. However, with this query:

SELECT * FROM table1 WHERE alc BETWEEN '7' AND '13';

yields no result. This is because the values are treated as string instead of numbers and when that happens, 1 is always smaller than 7. See below what happen you run select query with order by on the data set above:

SELECT * FROM table1 ORDER BY alc;

+-----+
| alc |
+-----+
|  11 |
|  13 |
|  7  |
|  9  |
+-----+

As you can see, since the data is treated as string (according to the column datatype), then you could imagine this in alphabetical form as the following:

+-----+--------------+
| alc | alphabetical |
+-----+--------------+
|  11 |      AA      |
|  13 |      AC      |
|  7  |       G      |
|  9  |       I      |
+-----+--------------+

So, the condition of BETWEEN '7' AND '13' becomes BETWEEN 'G' AND 'AC'; which doesn't really make sense. And if you change to BETWEEN '11' AND '9' you'll get the correct result but that made the query even more confusing and not making sense at all.

Now, I've discovered that there are at least 3 workaround/solution for this:

1. One of the oldest way I can think of is by adding +0 to the column in the query. I didn't find any official docs about this but I assume that doing this will change the data value to numeric in the query:

         SELECT * FROM table1 
            WHERE alc+0 BETWEEN '7' AND '13';

2. This is probably the same as above is just that I'm not sure if this is version specific or not. It turns out that in my testing, if you didn't wrap the searched value in quotes, you'll get the result as if the data is numeric:

         SELECT * FROM table1 
            WHERE alc BETWEEN 7 AND 13;

3. This require a change of column datatype but afterwards any of the query with or without quotes on the searched value should work:

         ALTER TABLE table1 CHANGE alc alc INT;

I hope that this is true and the issue is really about column datatype. As far as I know, this is the closest thing to what your situation is that I had experience with.

Here's a fiddle for reference