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?
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:
search() methodsearch() method calls a searchModels() methodsearchModels() method calls a buildSearchQuery() methodbuildSearchQuery() calls an initializeSearchQuery() method$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 \Laravel\Scout\Engines\DatabaseEngine 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 App\Custom;
use Laravel\Scout\Builder;
use Laravel\Scout\Engines\DatabaseEngine;
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 Laravel\Scout\EngineManager class. Inside the boot() method of your AppServierProvider class, you can do this, and here's an example:
<?php
namespace App\Providers;
use App\Custom\CustomDatabaseEngine;
use Illuminate\Support\ServiceProvider;
use Laravel\Scout\EngineManager;
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.