Multiple Where In

Question

I'm using eloqent to filter a set of products:

Product::whereIn('color', $color)->whereIn('size', $size)->whereIn('price', $price)->get();

Each of the above variables is an array of ids

$color = [1,2,4,5] 

My question is, is this inefficient when the user fails to send through a set of variables, say they did not want any color filters so the array would be:

$color = [];

I've tried ->toSql and it produces the sql statement:

select * from `products` where `color` in (?, ?) and 0 = 1 and `price` in (?, ?, ?, ?, ?)

In the above no size filter has been sent through.

What does 0 = 1 mean? And is this an inefficient way of handling things?

Answer 1

This is very interesting question.

0 = 1 will always be false, so your query will return zero rows. But why is this?

Because by setting

->whereIn('size', $size)

Laravel assumes that you always want the returned rows to be with one of the sizes in the passed array. If you do not pass any values in the array, Laravel can not do this where size IN () because it will be syntax error (you basically say give me all rows that match this size, but you do not pass size). So in case the array is empty it just puts 0 = 1.

In order to tell Laravel, if no size is passed, to not add condition for size just put a simple check before that.

$product = new Product;

if (!empty($sizes)) {
    $product = $product->whereIn('size', $sizes);
}

$products = $product->get();

Btw this behaviour is a hotfix. In previous versions of Laravel, if you pass empty array you just had an exception thrown for a syntax error. Now it is handled by just setting 1 = 0