多个地方 [英] Multiple Where In

问题描述

我正在使用eloqent过滤一组产品:

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

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

以上每个变量都是一个id数组

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 = [];

我尝试过-> toSql，它会产生sql语句:

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.

0 = 1是什么意思?这是处理事情的低效方法吗?

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

推荐答案

这是一个非常有趣的问题.

This is very interesting question.

0 = 1始终为false，因此您的查询将返回零行.但是为什么呢?

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

因为设置了

->whereIn('size', $size)

Laravel假定您始终希望返回的行具有传递的数组中的大小之一.如果您不传递数组中的任何值，Laravel将无法执行此where size IN ()，因为这将是语法错误(您基本上说给我所有与该大小匹配的行，但您不传递大小).因此，如果数组为空，则只需放入0 = 1.

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.

为了告诉Laravel，如果不传递尺寸，则不添加尺寸条件，只需在此之前进行简单检查即可.

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();

此行为是修补程序.在早期版本的Laravel中，如果传递空数组，则只会引发语法错误的异常.现在只需设置1 = 0

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

这篇关于多个地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！