Using Laravel eloquent to search if a value in a database column appears in search string

问题: I currently working on a laravel 5.4 project where I'm trying to find values in my database that are similar to a search string introduced. For example, I have the followin...

问题:

I currently working on a laravel 5.4 project where I'm trying to find values in my database that are similar to a search string introduced. For example, I have the following search term

$search_term = "Some long phrase with words"

and in my database (MySQL) I have a table tags with a column value. One row in this table would have value => 'some', another row might have value => 'long', and another row might have value => 'phra'

What I need is to create a collection of all tags where the value appears in my search term. Which means the 3 rows i mentioned should match for my $search_term

I currently know that I can use eloquent in laravel and say something like

Tag::where('value', 'like', "%".$search_term."%")->get()

But as far as I understand this would see if the $search_term is a sub-string of what's in the value column.

How can I use eloquent to ask if value is contained (a sub-string) in $search_term?


回答1:

You can use REGEXP, which is a regular expression search. However, since you're comparing the field to your variable, you're going to have to switch things around a bit and use a raw query:

 TAG::whereRaw('? REGEXP value', [$search_term])->get();

回答2:

I would prefer this, because not every string is valid regexp.

TAG::whereRaw('? LIKE CONCAT("%", value, "%")',
    [$search_term])->get();

回答3:

I know you asked for an eloquent solution, but maybe just solve it with php? You could do something like:

$tags = Tag::all();
$tags_found = collect([]);

foreach($tags as $tag) {
    if(strpos($search_term, $tag->value) !== false)
        $tags_found->push($tag)
}

回答4:

Split your search term into an array of single words using PHP.

$search_term = "Some long phrase, with words!";

$words = preg_replace('/W+/', ' ', $search_term);
$words = trim($words);
$words = explode(' ', $words);

Now $words contains

array (
  0 => 'Some',
  1 => 'long',
  2 => 'phrase',
  3 => 'with',
  4 => 'words',
)

And you can search with

Tag::whereIn('value', $words)->get();

Note: preg_replace('/W+/', ' ', $search_term); will replace any sequence of non-word characters with a single white space.

  • 发表于 2019-03-27 16:46
  • 阅读 ( 230 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除