Ronaldo100's avatar

Best Practice for SEARCHING through DB in Laravel

Hello! :)

What is the best way, to search for multiple words in an unsorted order in Laravel?

For example, let's assume the following values of a product listing:

DESIGNED BY: Mr. foo and Mrs. bar
TITLE:       Cute, white chair
DESCRIPTION: Funny-looking white chair with ornaments and unicorns

Now, please imagine a search bar allowing users to freely search for anything and everything.

For example: [ white cute funny unicorns ornaments bar foo ]

The tricky part about the search above is:

  1. The ORDER of the words is different than they are written in the database (e.g. "white" stands before "cute" and "bar" stands before "foo")
  2. We combine multiple DB columns.

How can I do that, please?

Below you will find what I have achieved so far:

For now, I have kept "designed by" and the other fields (title and description) separate, but I am willing to change it, of course.

Maybe I am on the wrong track altogether.

Perhaps I should go with something like ElasticSearch or whatever.

But, as I'm just learning Laravel, I am curious as to how to do it the right way in a Laravel kind of way.

Also, I wonder how to achieve rules like: "must contain ALL words" vs. "must contain ONE of the words" vs. "must contain the exact phrase" (which is especially tricky with multiple DB fields that get searched using ONE SINGLE search bar).

Again, I am at a total loss here.

routes/web.php

Route::get('/xyz', [ListingController::class, 'xyz']);

/app/Models/Listing.php

public function scopeFilter($query, array $filters)
{

        if($filters['designed_by'] ?? false)  // null-coalescing operator ??
        {
        $query->where('designed_by', 'like', '%' . request('designed_by') . '%');
        }
    
    
        if($filters['search'] ?? false)  // null-coalescing operator ??
        {
        $query->where('title', 'like', '%' . request('search') . '%')
            ->orWhere('description', 'like', '%' . request('search') . '%');
        }

}

/app/Http/Controllers/ListingController.php

public function xyz()
{
return view('xyz.index', [
    'listings' => Listing::latest()->filter(request(['designed_by', 'search']))->paginate(6)
]);

Thank you!

Yours

Ronaldo.

0 likes
2 replies
rodrigo.pedra's avatar

The snippet below might work. It splits a search string by space and add a where clause for each word.

But consider using some dedicated package for it, I recommend one of these:

public function scopeFilter($builder, array $filters)
{
    $builder->where(function ($query) use ($filters) {
        if (filled($filters['designed_by'] ?? null)) {
            $this->addSearchWhere($query, 'designed_by', $filters['designed_by']);
        }

        if (filled($filters['search'] ?? null)) {
            $this->addSearchWhere($query, 'title', $filters['search']);
            $this->addSearchWhere($query, 'description', $filters['search']);
        }
    });
}

private function addSearchWhere($builder, string $field, string $term)
{
    $field = $builder->getGrammar()->wrap($field);
    $words = preg_split('/\s+/', $term, PREG_SPLIT_NO_EMPTY);

    foreach ($words as $word) {
        $word = trim($word);
        $word = str_replace('\\', '\\\\\\', $word);
        $word = addcslashes($word, '%_');

        $builder->orWhereRaw("{$field} LIKE ? ESCAPE ?", ["%{$word}%", '\\']);
    }
}

EDIT fixed double escaping hidden by syntax highlighting

Please or to participate in this conversation.