Laravel Scout: search in multiple columns at once-CodePudding

I have a database with people. Their names are divided into first_name and last_name columns. I use Laravel Scout with database engine to search for their full names.

Let's say I have John Doe in the database. Right now, if I search for John, I get the result (Scout finds it in the first_name column). If I search for Doe, I also get the correct result (it is found in the last_name column). However, searching for John Doe gives no result, as it is divided into two columns.

I have found a possible solution here, but it doesn't work for me. I have made a trait, which combines the two name parts into one, and it works in blade components (I can say {{$person->fullName}}), but it doesn't work for Laravel Scout. This is my Person.php file:

<?php

namespace App\Models;

use Laravel\Scout\Searchable;
use App\Helpers\PersonAttributes;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Factories\HasFactory;

class Person extends Model
{
    use HasFactory, Searchable, PersonAttributes;

    public function toSearchableArray(): array
        {
            return [
                'fullName' => $this->fullName
            ];
        }
}

and this is my PersonAttributes.php file:

<?php

namespace App\Helpers;

trait PersonAttributes
{
    public function getFullNameAttribute(): string
    {
        return $this->first_name . " " . $this->last_name;
    }
}

The problem is, that when performing the search ($person = Person::search(request("query"))->get();), I get this error: Column not found: 1054 Unknown column 'persons.fullName' in 'where clause', as it is trying to perform this SQL:

select * from `persons` where (`persons`.`fullName` like %whateverisearchfor%) order by `id` desc

Any idea what I am doing wrong?

CodePudding user response：

It seems your query is trying to select values from DB, not from Elastic. First of all, check indexation. Try to use something like this:

$search = 'Jonh Doe'
$query[] = [
    'match_phrase_prefix' => [
        'fullName' => [
            'query' => $search,
          ],
    ],
];

$query = [
    'bool' => [
        'must' => $query,
    ],
];

$result = Person::searchQuery($query)->get();

Base information about Elastic

Hope it would lead you to the proper solution to your problem :)

CodePudding user response：

Looks like the easiest way to do it is not to use Laravel Scout at all and use just Eloquent instead. In the end, this is what I did:

$request = "%" . str_replace(" ", "%", request("query")) . "%";
$person_query = Person::query();
$person_query->where(DB::raw('concat(first_name, " ", last_name)'), 'like', $request);
$person = $person_query->get();