laravel search multiple words separated by space

Question

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

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.'%')

please help, thanks in advance

Answer 1

This is how you do it with Query\Builder, 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 ...

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();

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.

Answer 2

Have you considered using a FULLTEXT index on your first_name column?

You can create this index using a Laravel migration, although you need to use an SQL statement:

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).

If you want to find records containing all words, 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

Answer 3

 $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.

Answer 4

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);