mysql - Laravel 使用 AND/OR 运算符在多表上搜索多个关键字

Question

我有一个高级搜索表单，可以过滤 mySql 数据库中的结果。有一个部分是关键字搜索，它应该与所有相关表上的任何文本字段匹配。

这是所述搜索表单代码。

<form action="/search" method="POST">
   Keyword: <input type="text" name="keyword">
   Matching: <input type="radio" name="keywordControl" value="AND"> Match every words<br>
             <input type="radio" name="keywordControl" value="OR"> Match any words<br>
 <input type="submit">
</form>

表格:

CREATE TABLE `User` (
  `id` INT,
  `firstName` VARCHAR(100),
  `lastName` VARCHAR(100),
  `email` VARCHAR(100),
  `address` VARCHAR(400)
);

CREATE TABLE `savedLinks` (
  `id` INT,
  `userId` INT,
  `linkName` VARCHAR(100),
  `linkURL` VARCHAR(400),
  `linkNote` VARCHAR(100),
);

CREATE TABLE `BlogEntry` (
  `id` INT,
  `userId` INT,
  `entryTitle` VARCHAR(100),
  `entryExcerpt` VARCHAR(100),
  `entryBody` TEXT,
);

我需要什么:

如果我按这些参数搜索:

关键字:火影忍者area51

keywordControl:AND

我希望在所有表格中看到与 naruto 和 area51 都匹配的结果。

但是，我可以做的是过滤同一张表上的结果。我设法显示结果，如

• “Naruto先生”，地址为“Area51 Lane...”

• 任何保存了包含“Naruto”和“Area51”链接的人

• 任何撰写有关“火影忍者运行”和“Area51”博客的人

但我无法带来像这样的结果

• “Naruto先生”，发表了有关“Area51”的博客

• 任何保存过“火影忍者”链接并发表过“Area51”博客的人

因为后者需要搜索所有表。这是我的 Laravel Eloquent 代码。

$keyword = urldecode($request->input('keyword'));
$keywords = preg_split('/\s+/', $keyword, -1, PREG_SPLIT_NO_EMPTY);

$users = User::when(sizeof($keywords) > 0, function ($query) use ($keywords, $keywordControl) {
    $query->where(function ($query) use ($keywords, $keywordControl) {
        foreach ($keywords as $keyword) {
            if ($keywordControl == 'AND') {
                $query->whereRaw("CONCAT(firstName,lastName,email,address) LIKE ?", "%{$keyword}%");
            } else {
                $query->orWhereRaw("CONCAT(firstName,lastName,email,address) LIKE ?", "%{$keyword}%");
            }
        }
    });
})
->when(sizeof($keywords) > 0, function ($query) use ($keywords, $keywordControl) {
    $query->whereHas('savedLinks', function ($query) use ($keywords, $keywordControl) {
        $query->where(function ($query) use ($keywords, $keywordControl) {
            foreach ($keywords as $keyword) {
                if($keywordControl == 'AND'){
                    $query->whereRaw("CONCAT(linkName,linkURL,linkNote) LIKE ?", "%{$keyword}%");
                } else {
                    $query->orWhereRaw("CONCAT(linkName,linkURL,linkNote) LIKE ?", "%{$keyword}%");
                }
            }
        });
    });
})
->when(sizeof($keywords) > 0, function ($query) use ($keywords, $keywordControl) {
    $query->whereHas('blogEntry', function ($query) use ($keywords, $keywordControl) {
        $query->where(function ($query) use ($keywords, $keywordControl) {
            foreach ($keywords as $keyword) {
                if($keywordControl=='AND') {
                    $query->whereRaw("CONCAT(entryTitle,entryExcerpt,entryBody) LIKE ?", "%{$keyword}%");
                } else {
                    $query->orWhereRaw("CONCAT(entryTitle,entryExcerpt,entryBody) LIKE ?", "%{$keyword}%");
                }
            }
        });
    });
})
->get();

我的想法是，或者我是否应该创建另一个表并将所有这些文本连接到同一字段中以仅用于搜索目的。我不知道这是不是一个好主意。请指导一下，谢谢!

Answer 1

您是否定义了用户、SavedLinks 和博客的关系？对于此查询，用户必须与相关模型具有正确的关系:

# User model
public function savedLinks()
{
    return $this->hasMany(SavedLink::class, 'userId', 'id');
}

public function blogEntries()
{
    return $this->hasMany(BlogEntry::class, 'userId', 'id');
}

所有 when(...) 条件都是相同的。可以重写此查询以考虑到这一点。

此外，whereRaw() 接受 3 参数。如果您检查it's definition ，您将看到以下内容:

/**
 * Add a raw where clause to the query.
 *
 * @param  string  $sql
 * @param  mixed   $bindings
 * @param  string  $boolean
 * @return $this
 */
public function whereRaw($sql, $bindings = [], $boolean = 'and')
{
    $this->wheres[] = ['type' => 'raw', 'sql' => $sql, 'boolean' => $boolean];
    $this->addBinding((array) $bindings, 'where');
    return $this;
}

/**
 * Add a raw or where clause to the query.
 *
 * @param  string  $sql
 * @param  mixed   $bindings
 * @return \Illuminate\Database\Query\Builder|static
 */
public function orWhereRaw($sql, $bindings = [])
{
    return $this->whereRaw($sql, $bindings, 'or');
}

在我看来，您应该使用orWhereHas。您可以使用 empty() 代替 sizeof() 和 unless() ( it's just the opposite function ) 代替 when() 也使内容更具可读性。考虑到这一点，您的查询已经可以简化为这样，假设 $keywordControl 是 AND 或 OR:

$users = User::unless(empty($keywords), function ($query) use ($keywords, $keywordControl) {
    $query->where(function ($query) use ($keywords, $keywordControl) {
        foreach ($keywords as $keyword) {
            $query->whereRaw("CONCAT(firstName,lastName,email,address) LIKE ?", "%{$keyword}%", $keywordControl);
        }
    });
})
->orWhereHas('savedLinks', function ($query) use ($keywords, $keywordControl) {
    $query->where(function ($query) use ($keywords, $keywordControl) {
        foreach ($keywords as $keyword) {
            $query->whereRaw("CONCAT(linkName,linkURL,linkNote) LIKE ?", "%{$keyword}%", $keywordControl);
        }
    });
})
->orWhereHas('blogEntry', function ($query) use ($keywords, $keywordControl) {
    $query->where(function ($query) use ($keywords, $keywordControl) {
        foreach ($keywords as $keyword) {
            $query->whereRaw("CONCAT(entryTitle,entryExcerpt,entryBody) LIKE ?", "%{$keyword}%", $keywordControl);
        }
    });
})
->get();

我不知道用 whereRaw 连接事物然后进行比较是否比对每个属性使用 where 子句更快。

# Example 
->whereRaw("CONCAT(entryTitle,entryExcerpt,entryBody) LIKE ?", "%{$keyword}%", $keywordControl);
# vs
->where([
    ['entryTitle', 'like', "%{keyword}%", $keywordControl],
    ['entryExcerpt', 'like', "%{keyword}%", $keywordControl],
    ['entryBody', 'like', "%{keyword}%", $keywordControl],
])
# If someone could comment on this I'd be grateful.