多在何处 [英] 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?

推荐答案

这是个很有趣的问题.

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

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，如果没有传入 size，不添加 size 条件，只需在此之前简单检查一下即可.

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屋！