Laravel Scout search by multiple columns

Question

I have a user model with the following fields:

protected $fillable = [
    'name',
    'email',
    'password',
    'birthday',
    'surname'
];

I created a search bar to search the users. I'm using Laravel scout to query the users like this:

$matchingUsers = User::search($request->search)->get();

If I search with just the name or just the surname, the result is fine. The problem is when introducing the name and the surname. In this case, I get no results.

Expected result

Given the following records in my database:

-[id=>1 , name => "Neil", surname => "Armstrong"] -[id=>2 , name => "Juan Manuel", surname = "Armstrong"]

If I introduce "Neil Armstrong" I expect to get the user with id = 1.
If I search for "Juan Armstrong", I expect to get user with id = 2.
If I search for "Manuel", I expect to get the user with id = 2.

What I have tried so far

Using raw SQL directly querying in the database manager I got the expected result for situation 1 using the following query:

SELECT id, name, surname, image
FROM users
WHERE CONCAT(name,' ', surname) = "Neil Armstrong";

For the other two cases, I've tried to use the orWhere() method. However, this doesn't seem to work together, and anyway mixing raw SQL with eloquent and Scout seems so dirty.

Is there any way to use Laravel Scout to do something like this? A solution using just Eloquent wold also be useful.

Yes! I'm trying to use Eloquent or Scout. Any solution that actually works to get the expected result is fine. — Nil Suria, Jul 29 '22 at 12:50

Nick Dawes · Answer 1 · 2022-07-29T13:21:42.353

By default, the database engine will execute a "where like" query against every model attribute that you have configured as searchable.

So in your case, name and surname are model attributes, and a query of "Neil Amstrong" will check both for %Neil Armstrong%. Nothing matches, so no results.

You can use an accessor to create a new fullName model attribute, and add that attribute to the toSearchableArray() method on your User model.

Create a fullName attribute for your User model.

UserAttributes.php

trait UserAttributes
{
    public function getFullNameAttribute(): string
    {
        return $this->name." ".$this->surname;
    }
}

Then in your User model's toSearchableArray() method, include the fullName attribute.

User.php

...

use UserAttributes;

public function toSearchableArray(): array
{
    $searchable = [];

    $searchable["fullName"] = $this->fullName;
    // add more model attributes to search against...

    return $searchable;
}

...

Now when you query "Neil Armstrong", the database engine will search the fullName attribute and find your user.

This will work for the expected result 1, but not for 2 and 3. To get the expected result when doing searchs like 2 and 3, i managed to complete your solution with a “orWhere”. If there is no better answer in some days, I’m going to mark yours as correct. Thanks for your time! — Nil Suria, Jul 30 '22 at 15:24
may I ask you to take a look at a Laravel search related question here: https://stackoverflow.com/questions/76485513/laravel-search-with-multiple-keywords-against-multiple-columns-with-the-search ? — Istiaque Ahmed, Jun 15 '23 at 22:18

score 0 · Answer 2 · answered Jan 06 '23 at 17:47

I've had the same problem, and eventually, I've completely removed Scout from my Laravel project and did it just with Eloquent:

$user_query = User::query();
$user_query->where(DB::raw('concat(name, " ", surname)'), 'like', $request);
$user = $user_query->get();

For the $request, I did:

$request = "%" . str_replace(" ", "%", request("query")) . "%";

With this, all three of the points you mentioned should be achieved.

Laravel Scout search by multiple columns

2 Answers2

Linked