Search multile words name in columns using laravel-CodePudding

Is there any better solution to the code below, I want to search names but first name and last name is different columns as of now I have this

->where(function($query) use($q){
            $names = explode(" ", $q);
            if(count($names) == 2){
                $query->where('fname', 'LIKE', '%' . $names[0] . '%')
                ->where('lname', 'LIKE', '%' . $names[1] . '%');
            }
            else {
                $query->where('fname', 'LIKE', '%' . $q . '%')
                ->orWhere('lname', 'LIKE', '%' . $q . '%');
            }
        });

but the result is only for firstname on the 1st array and the last name is on the 2nd array value, how would do it to make it sideways? or What if there is 2 words in 1st name and 2 words in last name?

CodePudding user response：

You can try this:

Firstly concatenate first name and last name then apply search on them.

$result = User::where(DB::raw('concat(first_name," ",last_name)') , 'LIKE' , '%keyword%')->get();

CodePudding user response：

Here's a more flexible solution to search by first name and last name:

->where(function($query) use($q) {
    $name_wildcard = str_replace(' ', '%', $q);
    $name_wildcard = '%' . $name_wildcard . '%';

    $query->whereRaw('CONCAT(first_name," ",last_name) LIKE ?"', [$name_wildcard]);
});

Here, if you have a user in the table:

First Name: Juan Joseph
Last Name: Dela Cruz

If you search for "Juan Cruz", it will show up