I’m looking at the database layer of Kirby and could not figure how to get BETWEEN to work. I tried to do
Db::table('products')->where('quantity', 'BETWEEN', [10, 20])->all()
but this gives me an error Uncaught InvalidArgumentException: Invalid predicate BETWEEN
Looking at the code on GitHub it has BETWEEN and NOT BETWEEN listed as predicates - so what is the syntax to get it to work?
Given that syntax for BETWEEN is
WHERE `xy` BETWEEN x AND y
I’d try a string as argument.
Db::table('products')->where('quantity', 'BETWEEN', '10 AND 20')->all()
That doesn’t work as ‘10 AND 20’ is passed as a single string parameter.
Hm, with this syntax it works:
$products = Db::select('products', '*', 'quantity', between "10" and "20"');
Or
$products = Db::table('products')->where('quantity between 10 and 20')->all();
Or
$products = Db::table('products')->where('quantity between :quantity_1 and :quantity_1', ['quantity_1' => 10, 'quantity_1' => 20])->all();
No idea how that other syntax is supposed to work, if at all, unfortunately there is no test for it.
Ping @lukasbestle
Looks like a bug to me. I’d say passing an array with the minimum and maximum values like in @SeriousKen’s code example would be the intended behavior, but if the third argument is an array, the logic ends up in another conditional section that only supports IN and NOT IN. Having BETWEEN in the section that accepts anything but arrays doesn’t make sense to me.
Could you please open an issue on GitHub?