Laravel 多个 whereHas 回调中的 where 条件

Laravel multiple where condition in whereHas callback

提问by AskMan

I am creating a general search function with Laravel 5.2 and I want to display all the books in which occurs the searched keyword in: the book's title, book's subject, book's plot, book's author name, book's author surname; I thought that this code would work:

我正在使用 Laravel 5.2 创建一个通用搜索功能，我想显示所有出现搜索关键字的书籍：书名，书的主题，书的情节，书的作者姓名，书的作者姓氏；我认为这段代码会起作用：

$results = Book::whereHas('author', function ($query) use ($keyword)
                     {
                          $query->where('surname', 'LIKE', '%'.$keyword.'%')
                               ->orWhere('name', 'LIKE', '%'.$keyword.'%');
                     })
                     ->orWhere('title', 'LIKE', '%'.$keyword.'%')
                     ->orWhere('plot', 'LIKE', '%'.$keyword.'%')
                     ->orWhere('subject', 'LIKE', '%'.$keyword.'%')
                     ->get();

But when I use as a keyword the name of an author, I get as a results the whole library. Instead if I enter the surname of an author it works perfectly.

但是当我使用作者的名字作为关键字时，我会得到整个图书馆的结果。相反，如果我输入作者的姓氏，它就完美无缺。

I found out this solution, that is not optimal in my opinion, but at least it works:

我发现了这个解决方案，在我看来这不是最佳的，但至少它有效：

$results = Book::whereHas('author', function ($query) use ($keyword)
                     {
                          $query->where('surname', 'LIKE', '%'.$keyword.'%');
                     })
                     ->orWhereHas('author', function ($query) use ($keyword)
                     {
                          $query->where('name', 'LIKE', '%'.$keyword.'%');
                     })
                     ->orWhere('title', 'LIKE', '%'.$keyword.'%')
                     ->orWhere('plot', 'LIKE', '%'.$keyword.'%')
                     ->orWhere('subject', 'LIKE', '%'.$keyword.'%')
                     ->get();

Any suggestions? Thank you in advance for your help!

有什么建议？预先感谢您的帮助！

回答by patricus

The issue is that the where clauses added in your closure aren't the only where clauses being applied to the subquery. The whereHas()method generates a subquery that starts with a where clause on the ids for the relationship. Because of this, your subquery isn't just where x or y, it is actually where x and y or z.

问题是在您的闭包中添加的 where 子句并不是应用于子查询的唯一 where 子句。该whereHas()方法生成一个子查询，该子查询以关系 id 上的 where 子句开头。因此，您的子查询不仅仅是where x or y，它实际上是where x and y or z。

Given this set of where clauses, and the order of operations for logical operators, if the zcondition is true (your 'name' condition), the whole where clause will return true, meaning the constraint on only looking at related objects is completely ignored. Since the constraint on related objects is ignored, the hascondition will be true for every record (if 'name' matches any record).

给定这组 where 子句，以及逻辑运算符的操作顺序，如果z条件为真（您的“名称”条件），则整个 where 子句将返回真，这意味着完全忽略仅查看相关对象的约束。由于对相关对象的约束被忽略，因此has每个记录的条件都为真（如果“名称”与任何记录匹配）。

Below is an example of your logical conditions:

以下是您的逻辑条件示例：

// first boolean is the related keys check
// second boolean is the surname check
// third boolean is the name check

// this is your current logic
// as you can see, this returns true even when looking at an
// author not even related to the book.
var_export(false && false || true); // true

// this is what your logic needs to be
var_export(false && (false || true)); // false

So, to solve this issue, you need to wrap your orconditions in parentheses, so they're evaluated as you intended. You can do this by passing a closure to the where()method, and then any conditions added inside the closure will be inside parentheses:

因此，要解决此问题，您需要将or条件括在括号中，以便按预期对它们进行评估。您可以通过将闭包传递给where()方法来完成此操作，然后在闭包内添加的任何条件都将在括号内：

$results = Book::whereHas('author', function ($query) use ($keyword) {
        $query->where(function ($q) use ($keyword) {
            $q->where('surname', 'LIKE', '%'.$keyword.'%')
                ->orWhere('name', 'LIKE', '%'.$keyword.'%');
        });
    })
    ->orWhere('title', 'LIKE', '%'.$keyword.'%')
    ->orWhere('plot', 'LIKE', '%'.$keyword.'%')
    ->orWhere('subject', 'LIKE', '%'.$keyword.'%')
    ->get();

回答by ExoticChimp

have you tried creating a local query scope for your author/user model such as named() then applying that

您是否尝试过为您的作者/用户模型创建本地查询范围，例如 named() 然后应用该范围

public function scopeNamed($query)
{
    return $query->where('surname', 'LIKE', '%'.$keyword.'%')
                 ->orWhere('name', 'LIKE', '%'.$keyword.'%');
}

Then your query

然后你的查询

$results = Book::whereHas('author', function ($query) use ($keyword)
                 {
                      $query->named();
                 })
                 ->orWhere('title', 'LIKE', '%'.$keyword.'%')
                 ->orWhere('plot', 'LIKE', '%'.$keyword.'%')
                 ->orWhere('subject', 'LIKE', '%'.$keyword.'%')
                 ->get();

https://laravel.com/docs/master/eloquent#query-scopes

https://laravel.com/docs/master/eloquent#query-scopes