laravel 搜索多个以空格分隔的单词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28270244/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
laravel search multiple words separated by space
提问by user4006175
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
我是 Laravel 查询构建器的新手，我想搜索在输入字段中输入的多个单词，例如，如果我输入“jhon doe”，我想获取包含 jhon 或 doe 的任何列
I have seen/tried solutions using php MySQL but can't able to adapt to query builder
我已经看到/尝试过使用 php MySQL 的解决方案，但无法适应查询构建器
//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.'%')
please help, thanks in advance
请帮忙，提前致谢
回答by Jarek Tkaczyk
This is how you do it with Query\Builder, but first some additional notes:
这就是你如何使用Query\Builder，但首先要注意一些额外的注意事项：
// 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 ...
That said, you obviously can't rely on explodebecause in the above case you would get all the rows.
也就是说，您显然不能依赖，explode因为在上述情况下，您将获得所有行。
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();
There is closure in the wherebecause it is a good practice to wrap your or whereclauses in parentheses. For example if your Usermodel used SoftDeletingScopeand you would not do what I suggested, your whole query would be messed up.
在 the 中有闭包，where因为将or where子句括在括号中是一种很好的做法。例如，如果您User使用了模型SoftDeletingScope并且您不按照我的建议进行操作，那么您的整个查询就会搞砸。
回答by Sameer Shaikh
$keywordRaw = "jhon doe";
$key = explode(' ',$keywordRaw)
$users = User::select('users.*')
->whereIn('first_name',$key);
This would work.the whereIn would search for first name from the keywords you entered.
这会起作用。 whereIn 将从您输入的关键字中搜索名字。
回答by EthanHu
You can try as follows
您可以尝试如下
$keywordRaw = "jhon doe";
$bindArr = explode(" ", $keywordRaw);
$query = "select * from users where";
foreach ($i = 0; $i < count($bindArr); $i++) {
if ($i == 0) {
$query.= ' first_name LIKE "%?%"';
} else {
$query.= ' or first_name LIKE "%?%"';
}
}
$sth = $dbh->prepare($query);
$sth->execute($bindArr);
回答by Deenadhayalan Manoharan
Try this..
尝试这个..
$searchQuery = "jhon doe";
$searchTerms = explode(" ", $searchQuery); // Split the words
$users = User::whereIn('FirstName', $searchTerms)->get();
print_r($users);
回答by sevenpointsix
Have you considered using a FULLTEXT index on your first_name column?
您是否考虑过在 first_name 列上使用 FULLTEXT 索引？
You can create this index using a Laravel migration, although you need to use an SQL statement:
您可以使用 Laravel 迁移创建此索引，但您需要使用 SQL 语句：
DB::statement('ALTER TABLE users ADD FULLTEXT(first_name);');
You can then run quite advanced searches against this field, like this:
然后，您可以针对该字段运行非常高级的搜索，如下所示：
$keywordRaw = "john doe";
$keywords = explode(' ', $keywordRaw);
$users = User::select("*")
->whereRaw("MATCH (first_name)
against (? in boolean mode)",[$keywords])
->get();
That will match records containing either the words "john" or "doe"; note that this approach will match on whole words, rather than substrings (which can be the case if you use LIKE).
这将匹配包含单词“john”或“doe”的记录；请注意，此方法将匹配整个单词，而不是子字符串（如果您使用 LIKE，则可能是这种情况）。
If you want to find records containing allwords, you should precede each keyword with a '+', like this:
如果您想查找包含所有单词的记录，您应该在每个关键字前加上一个“+”，如下所示：
$keywords = '+'.explode(' +', $keywordRaw);
You can even sort by relevance, although this is probably overkill for your needs (and irrelevant for "all" searches). Something like this:
您甚至可以按相关性排序，尽管这对于您的需求来说可能有点过分（并且与“所有”搜索无关）。像这样的东西：
$users = User::select("*")
->selectRaw("MATCH (first_name)
against (? in boolean mode)
AS relevance",[$keywords])
->whereRaw("MATCH (first_name)
against (? in boolean mode)",[$keywords])
->orderBy('relevance','DESC')
->get();
There is a good article that covers this general approach here:
有一篇很好的文章在这里介绍了这种一般方法：
http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes
http://www.hackingwithphp.com/9/3/18/advanced-text-searching-using-full-text-indexes
回答by C.V
You can use this package https://github.com/nicolaslopezj/searchable
你可以使用这个包https://github.com/nicolaslopezj/searchable
or just do this, if you don't want to use package
或者只是这样做，如果你不想使用包
$keywordRaw = "jhon doe";
$users = User::where('first_name', 'LIKE', $keywordRaw.'%')
->orWhere('first_name', 'LIKE', '% '.$keywordRaw.'%')
->get();
回答by Jerome
$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->Where('name', 'like', "%{$value}%");
}
})->get();
Using 'orWhere' will get you results for each keyword not results for keyword1 + keyword2...So all depends on what your looking for
使用 'orWhere' 将为您提供每个关键字的结果，而不是关键字 1 + 关键字 2 的结果......所以一切都取决于您要寻找的内容
