Laravel Question posted in
The official Laravel Documentation can be found here.

Search for multiple words in Laravel Scout

I am currently using Laravel Scout to search through my models with the database driver

My search controller looks like this

Post::search($request->filter)->get();

Lets say I have a posts with a Text that say "Hello this is a Post"

When I am searching for "Hello" or "Post" individually it works. But when I am searching for "Hello Post" together it doesnt because there are other words in between and the executed query column WHERE LIKE %Hello Post% which is called by the search method is not finding matches. Is there a way to check if the text in the model contains all words of my query string regardless of their order and appearence? For me it seems like I must find a way to make a query like column WHERE LIKE %Hello% AND column WHERE LIKE %Post%.

I tried to convert the search query into an array separated by empty space like explode(' ', $request->filter). However the search method on the model does not accept an Array. Only a string. I tried to loop and search over the elements of each array item individually like

foreach(explode(' ', $request->filter) as $filter) {
  $results = Post::search($filter)->get();
}

but its giving me the wrong results because I need the post where all words fit and not just individual ones. I have considered to filter the results with Str::containsAll($column, explode(' ', $request->filter) but this is not the desired solution.

I need a way to make the correct Database query as mentioned above like column WHERE LIKE %Hello% AND column WHERE LIKE %Post% but how do I achieve this with Laravel Scout?

Tags:

2

Answers


  1. 0

    Modify the Search Query:

    use IlluminateHttpRequest;
use AppModelsPost;

public function search(Request $request)
{
    $query = Post::query();  // Start with a base query

    $words = explode(' ', $request->input('filter'));  // Split the filter input into words
    foreach ($words as $word) {
        $query->where('text', 'LIKE', '%' . $word . '%');  // Add a LIKE condition for each word
    }

    $results = $query->get();  // Execute the query

    return view('search.results', ['posts' => $results]);  // Return the results to a view
}

    In this case you can ensures that the SQL executed behind the scenes would look like this:

    SELECT * FROM posts WHERE text LIKE '%Hello%' AND text LIKE '%Post%';

    But to be sure Test the search functionality:
    Single word searches, Searches with common words and Empty search inputs.

    But if you notice the search is slow:

    Take a look if your database text columns are indexed appropriately. However, note that LIKE ‘%word%’ queries do not generally benefit from traditional indexes. But take a look anyway.

    Consider use full-text search capabilities if your database supports them (like MySQL’s FULLTEXT index or PostgreSQL’s text search features). Laravel Scout can be configured to use these features if set up correctly.

    Using raw LIKE queries might be inefficient for large datasets or columns. In such cases, consider switching to a more robust search driver supported by Laravel Scout, such as Algolia, MeiliSearch, or even database-specific text search.

    And using a caching solution like Redis can significantly improve the performance of your search functionality.

    Adding it to make after your comment:

    To make sure the solution works with any Scout driver, you can create a Custom Search Method:

    namespace AppModels;

use LaravelScoutSearchable;
use IlluminateDatabaseEloquentModel;

class Post extends Model
{
    use Searchable;

    /**
     * Perform a full-text search across multiple drivers.
     */
    public static function fullTextSearch($query)
    {
        return static::search($query)->query(function ($builder) use ($query) {
            $words = explode(' ', $query);
            foreach ($words as $word) {
                $builder->where('text', 'LIKE', '%' . $word . '%');
            }
        });
    }
}

    and so use custom search in Controller:

    use IlluminateHttpRequest;
use AppModelsPost;

public function search(Request $request)
{
    $results = Post::fullTextSearch($request->input('filter'))->get();
    return view('search.results', ['posts' => $results]);
}
    Login or Signup to reply.
  2. 0

    Unfortunately, the database driver that ships with Laravel Scout doesn’t support what you’re trying to do because at the end of the day, this driver is only a wrapper around the WHERE LIKE statement in MySQL, so when you feed it, for example, the query Hello Post, it will yield the following SQL statement as you said:

    SELECT columns FROM `posts` WHERE column LIKE `%Hello Post%`

    However, there’s a solution but it’s a little bit complicated, but I’m going to explain it in detail.

    The solution would be to create a custom scout driver to alter this behavior. Don’t worry, it’s harder than it looks.

    Scout uses the concepts of engines to manage search drivers, in this link you can view a set of available drivers in Laravel Scout. When you set the SCOUT_DRIVER variable to be database you’re telling Scout to use the DatabaseEngine class for all its underlying operations.

    Inside this class, we can see the following:

    • We have a search() method
    • The search() method calls a searchModels() method
    • The searchModels() method calls a buildSearchQuery() method
    • The buildSearchQuery() calls an initializeSearchQuery() method
    • At line 224 you can see the following piece of code:
    $query->orWhere(
    $builder->model->qualifyColumn($column),
    $likeOperator,

    // NOTICE THE FOLLOWING LINE
    in_array($column, $prefixColumns) ? $builder->query.'%' : '%'.$builder->query.'%',
);

    As you can see, whatever search query you pass to the search() method on your model will be passed down here in the end, so what we’ll need to do is to alter this behavior a little bit by splitting the incoming search query using the explode() method and the whitespace as the separator, trim the resultant keywords, and join them using the implode() method again but use the % operator as the separator.

    Create a CustomDatabaseEngine class inside your app folder, this class should extend the LaravelScoutEnginesDatabaseEngine class so that we keep all the benefits of the original database driver, and remember that we’re only interested in overriding the initializeSearchQuery() method. Here’s a suggested implementation:

    <?php

namespace AppCustom;

use LaravelScoutBuilder;
use LaravelScoutEnginesDatabaseEngine;

class CustomDatabaseEngine extends DatabaseEngine
{
    protected function initializeSearchQuery(Builder $builder, array $columns, array $prefixColumns = [], array $fullTextColumns = [])
    {
        if (blank($builder->query)) {
            return $builder->model->newQuery();
        }

        return $builder->model->newQuery()->where(function ($query) use ($builder, $columns, $prefixColumns, $fullTextColumns) {
            $connectionType = $builder->model->getConnection()->getDriverName();

            $canSearchPrimaryKey = ctype_digit($builder->query) &&
                in_array($builder->model->getKeyType(), ['int', 'integer']) &&
                ($connectionType != 'pgsql' || $builder->query <= PHP_INT_MAX) &&
                in_array($builder->model->getKeyName(), $columns);

            if ($canSearchPrimaryKey) {
                $query->orWhere($builder->model->getQualifiedKeyName(), $builder->query);
            }

            $likeOperator = $connectionType == 'pgsql' ? 'ilike' : 'like';

            foreach ($columns as $column) {
                if (in_array($column, $fullTextColumns)) {
                    $query->orWhereFullText(
                        $builder->model->qualifyColumn($column),
                        $builder->query,
                        $this->getFullTextOptions($builder)
                    );
                } else {
                    if ($canSearchPrimaryKey && $column === $builder->model->getKeyName()) {
                        continue;
                    }

                    // START OF MODIFICATIONS

                    // 1. Split the incoming search query by whitespace
                    $keywords = explode(" ", $builder->query);

                    // 2. Trim each resultant keyword from whitespace and carriage return characters
                    $keywords = array_map(function($keyword) {
                        return trim($keyword);
                    }, $keywords);

                    // 3. Re-join these keywords together and use the % operator so that MySQL interprets it
                    $searchQuery = implode("%", $keywords);

                    // 4. Pass the modified version of the search query to the orWhere() method
                    $query->orWhere(
                        $builder->model->qualifyColumn($column),
                        $likeOperator,
                        in_array($column, $prefixColumns) ? $searchQuery.'%' : '%'.$searchQuery.'%',
                    );

                    // END OF MODIFICATIONS
                }
            }
        });
    }
}

    Now you need to register this custom driver, this can be done using the extend() method offered by the LaravelScoutEngineManager class. Inside the boot() method of your AppServierProvider class, you can do this, and here’s an example:

    <?php

namespace AppProviders;

use AppCustomCustomDatabaseEngine;
use IlluminateSupportServiceProvider;
use LaravelScoutEngineManager;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     */
    public function register(): void
    {
        //
    }

    /**
     * Bootstrap any application services.
     */
    public function boot(): void
    {
        app(EngineManager::class)->extend("custom_database", function() {
            return new CustomDatabaseEngine;
        });
    }
}

    The first parameter of the extend() method will be the name of the driver, and the second defines the binding class of this driver. Now open up your .env file and change the SCOUT_DRIVER to custom_database:

    SCOUT_DRIVER=custom_database

    And that’s it, now if you have a column that contains the value Hello World This Is My Post and you try to run the code:

    Post::search('Hello Post')->get();

    You should get the desired result.

    If you have any questions or need any clarification, ask me in the comments.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search