laravel 搜索多个以空格分隔的单词

2021-12-26 00:00:00 php mysql laravel eloquent query-builder

我是 laravel 查询构建器的新手，我想搜索在输入字段中输入的多个单词，例如，如果我输入jhon doe"，我想获取包含 jhon 或 doe 的任何列

I am new to laravel query builder, I want to search multiple words entered in an input field for example if I type "jhon doe" I want to get any column that contains jhon or doe

我已经看到/尝试过使用 php MySQL 的解决方案，但无法适应查询构建器

I have seen/tried solutions using php MySQL but can't able to adapt to query builder

//1. exploding the space between the keywords 

//2. using foreach apend the query together

$query = "select * from users where";

$keywordRaw = "jhon doe";
$keywords = explode(' ', $keywordRaw );
foreach ($keywords as $keyword){
$query.= " first_name LIKE '%" + $keyword +"%' OR ";
}

如何使用查询构建器执行此操作

how do I do this using query builder

这是我到目前为止所拥有的，这样做的正确方法是什么，

this is what i have so far, what is the proper way of doing this, 

$keywordRaw = "jhon doe";
//how do I explode this words and append them along with their appropriate query
$users = User::select('users.*')
->where('first_name', 'LIKE', '%'.$keywordRaw.'%')

请帮忙，提前致谢

推荐答案

这就是您使用 QueryBuilder 的方式，但首先要补充一些注意事项:

This is how you do it with QueryBuilder, but first some additional notes:

// user can provide double space by accident, or on purpose:
$string = 'john  doe';

// so with explode you get this:
explode(' ', $string);
array(
  0 => 'john',
  1 => '',
  2 => 'doe'
)

// Now if you go with LIKE '%'.value.'%', you get this:
select * from table where name like '%john%' or name like '%%' or ...

也就是说，您显然不能依赖 explode，因为在上述情况下，您将获得所有行.

That said, you obviously can't rely on explode because in the above case you would get all the rows.

所以，这是你应该做的:

So, this is what you should do:

$string = 'john  doe';

// split on 1+ whitespace & ignore empty (eg. trailing space)
$searchValues = preg_split('/s+/', $string, -1, PREG_SPLIT_NO_EMPTY); 

$users = User::where(function ($q) use ($searchValues) {
  foreach ($searchValues as $value) {
    $q->orWhere('name', 'like', "%{$value}%");
  }
})->get();

where 中有闭包，因为将 或 where 子句括在括号中是一种很好的做法.例如，如果您的 User 模型使用了 SoftDeletingScope 而您不按照我的建议去做，那么您的整个查询就会搞砸.

There is closure in the where because it is a good practice to wrap your or where clauses in parentheses. For example if your User model used SoftDeletingScope and you would not do what I suggested, your whole query would be messed up.

相关文章