Several places in

Question

I use 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 identifiers

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

My question is that it is inefficient when the user cannot send through a set of variables, let's say that they do not need color filters, so the array will be:

 $color = []; 

I tried → toSql and it issues the sql statement:

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

In the format above, the size filter has not been sent.

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

Answer 1

This is a very interesting question.

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

Because by setting

 ->whereIn('size', $size) 

Laravel assumes that you always want the returned rows to be one of the sizes in the passed array. If you don't pass any values ​​in the array, Laravel can't do it where size IN () , because it will be a syntax error (you basically say give me all the rows matching that size, but you won't miss the size). Therefore, if the array is empty, it simply puts 0 = 1 .

To tell Laravel if the size is not passed, so as not to add a condition for the size, just put a simple check in front of it.

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

Btw this behavior is a fix . In previous versions of Laravel, if you passed an empty array, you just had an exception for a syntax error. Now it is only processed by setting 1 = 0