Better multi-keyword searching

[kduma]

Hello,

Currently the search give somewhat not fully expected results. For example when searching for "John Smith" we currently get all John's and all Smith's in a database. I (and probably most of users) would expect getting only John' Smith's as the query result. The problem is that currently generated SQL looks like:

SELECT * FROM users WHERE (
    first_name LIKE '%JOHN%' 
    OR 
    first_name LIKE '%SMITH%' 
    OR 
    last_name LIKE '%JOHN%' 
    OR 
    last_name LIKE '%SMITH%'
)

Much preferred (at least by me - discussion appreciated) would be a form like:

SELECT * FROM users WHERE (
    (
        first_name LIKE '%JOHN%' 
        OR 
        last_name LIKE '%JOHN%' 
    )
    AND 
    (
        first_name LIKE '%SMITH%' 
        OR 
        last_name LIKE '%SMITH%'
    )
)

In my own experimentation, I've created small modification which changes the behavior of ModelSearchAspect to meet my own expectations. Now I'm sharing it here as an example or possible solution for people will need it.

class ExtendedModelSearchAspect extends ModelSearchAspect
{
    protected function addSearchConditions(Builder $query, string $term)
    {
        $attributes = $this->attributes;
        $searchTerms = explode(' ', $term);

        $searchTerms = collect($searchTerms)->map(function ($q){
            return trim($q, " \t\n\r\0\x0B'\"-_)(*&^%$#@!?/><.,:;}]{[|\\+=~`");
        })->filter();


        $searchTerms->map(function ($searchTerm) use ($attributes, $query) {
            $query->where(function (Builder $query) use ($attributes, $searchTerm) {
                foreach (Arr::wrap($attributes) as $attribute) {
                    $sql = "LOWER(`{$attribute->getAttribute()}`) LIKE ?";
                    $searchTerm = mb_strtolower($searchTerm, 'UTF8');

                    $attribute->isPartial()
                        ? $query->orWhereRaw($sql, ["%{$searchTerm}%"])
                        : $query->orWhere($attribute->getAttribute(), $searchTerm);
                }
            });
        });
    }
}

[sawirricardo]

Nice solution, how do you use it? @kduma

[kduma]

Like that:

 $searchResults = (new Search())
    ->registerAspect(new ExtendedModelSearchAspect(User::class, function (ExtendedModelSearchAspect $modelSearchAspect) {
        $modelSearchAspect
            ->addSearchableAttribute('first_name')
            ->addSearchableAttribute('last_name');
    }))
    ->search($query);