baril / sqlout
MySQL fulltext driver for Laravel Scout.
Installs: 9 158
Dependents: 0
Suggesters: 0
Security: 0
Stars: 43
Watchers: 4
Forks: 6
Open Issues: 0
Requires
- php: ^7.3|^8.0
- illuminate/database: ^8.79|^9.46|^10.0|^11.0|^12.0
- illuminate/support: ^8.79|^9.46|^10.0|^11.0|^12.0
- laravel/scout: ^9.0|^10.0
Requires (Dev)
- orchestra/testbench: ^6.23|^7.0|^8.0|^9.0|^10.0
- phpunit/phpunit: ^9.0|^10.0|^11.0
- squizlabs/php_codesniffer: ^3.7
- voku/stop-words: ^2.0
- wamania/php-stemmer: ^3.0|^4.0
- yooper/stop-words: ^1.0
Suggests
- voku/stop-words: A collection of stop words for various languages.
- wamania/php-stemmer: PHP stemmer that can be used together with Sqlout.
- yooper/stop-words: Another collection of stop words.
README
Sqlout is a MySQL driver for Laravel Scout. It indexes the data into a dedicated table of the MySQL database, and uses a fulltext index to search. It is meant for small-sized projects, for which bigger solutions such as ElasticSearch would be an overkill.
Sqlout is different than Scout's native Database
engine because it indexes
data in a separate, dedicated table, and uses a fulltext index. Sqlout has more
features such as field weights and word stemming.
Sqlout is compatible with Laravel 5.8+ to 12.x and Scout 7.1+ / 8.x / 9.x / 10.x (credit goes to ikari7789 for Laravel 9 and 10 / Scout 9 and 10 support).
You can find the full API documentation here.
Version compatibility
Laravel | Scout | Sqlout |
---|---|---|
11.x / 12.x | 10.x | 5.1+ |
9.x / 10.x | 10.x | 5.x |
8.x / 9.x | 9.x | 4.x |
8.x | 8.x | 3.x |
7.x | 8.x | 2.0 |
6.x | 8.x | 2.0 |
6.x | 7.1 / 7.2 | 1.x / 2.0 |
5.8 | 7.1 / 7.2 | 1.x / 2.0 |
Setup
Require the package:
composer require baril/sqlout
Publish the configuration:
php artisan vendor:publish
If you're not using package discovery, manually add the service providers
(Scout's and Sqlout's) to your config/app.php
file:
return [ // ... 'providers' => [ // ... Laravel\Scout\ScoutServiceProvider::class, Baril\Sqlout\SqloutServiceProvider::class, ], ];
Creating the index
Models are indexed in a separate table. You can either create a single table and use it as a global index for all models, or create 1 index table per model (or a mix of both).
Global index
The default name for the table is searchindex
. If you want to use a different
name, you can configure it in config/scout.php
:
return [ // ... 'sqlout' => [ 'table_name' => 'my_custom_index_name', // ... ], // ... ];
Then, migrate your database:
php artisan sqlout:make-migration php artisan migrate
This will create the searchindex
table (or whatever name you've configured).
Different connections
If you want to index models that belong to different connections, you need
an index table per connection. To create the table on a connection that
is not the default connection, you can call the sqlout:make-migration
command
and pass the name of the connection:
php artisan sqlout:make-migration my_other_connection php artisan migrate
Separate indexes
If you prefer to index each model in a different table, set the global index name to an empty string:
return [ // ... 'sqlout' => [ 'table_name' => '', // ... ], // ... ];
Each model will be indexed in a table named like the model table
followed by _index
, eg. the Post
model will be index in posts_index
.
You can configure a different suffix, and also a prefix, in the config file:
return [ 'prefix' => 'sqlout_', 'suffix' => '', ];
You can also customize the table name for each model with the searchableAs
method
(see next section).
Once you're set up, create the index table for your models like this:
php artisan sqlout:make-migration --model="\\App\\Models\\Post"
php artisan migrate
Making a model searchable
namespace App\Models; use Baril\Sqlout\Searchable; class Post extends Model { use Searchable; protected $weights = [ 'title' => 4, 'excerpt' => 2, ]; public function toSearchableArray() { return [ 'title' => $this->post_title, 'excerpt' => $this->post_excerpt, 'body' => $this->post_content, ]; } // Optionally, you can customize the // name of the table that the model // will be indexed in: public function searchableAs(): string { return 'my_custom_index'; } }
The example above is similar to what is described in Scout's documentation, with the following differences/additions:
- The model uses the
Baril\Sqlout\Searchable
trait instead ofLaravel\Scout\Searchable
. - The
$weight
property can be used to "boost" some fields. The default value is 1.
Once this is done, you can index your data using Scout's Artisan command:
php artisan scout:import "App\\Models\\Post"
Your models will also be indexed automatically on save.
Searching
Basics
$results = Post::search('this rug really tied the room together')->get(); $results = Post::search('the dude abides')->withTrashed()->get();
See Scout's documentation for more details.
Sqlout's builder also provides the following additional methods:
// Restrict the search to some fields only: $builder->only('title'); $builder->only(['title', 'excerpt']); // (use the same names as in the toSearchableArray method) // Retrieve the total number of results: $nbHits = $builder->count();
Using scopes
With Sqlout, you can also use your model scopes on the search builder,
as if it was a query builder on the model itself. Similarly, all calls to the
where
method on the search builder will be
forwarded to the model's query builder.
$results = Post::search('you see what happens larry') ->published() // the `published` scope is defined in the Post class ->where('date', '>', '2010-10-10') ->get();
⚠️ Keep in mind that these forwarded scopes will actually be applied to a subquery (the main query here being the one on the
searchindex
table). This means that for example a scope that adds anorder by
clause won't have any effect. See below for the proper way to order results.
If the name of your scope collides with the name of a method of the
Baril\Sqlout\Builder
object, you still have the option to use Scout's
query
method:
$results = Post::search('ve vant ze money lebowski') ->query(function ($query) { $query->within('something'); }) ->get();
Search modes
MySQL's fulltext search comes in 3 flavours:
- natural language mode,
- natural language mode with query expansion,
- boolean mode.
Sqlout's default mode is "natural language" (but this can be changed in the config file).
You can also switch between all 3 modes on a per-query basis, by using the following methods:
$builder->inNaturalLanguageMode(); $builder->withQueryExpansion(); $builder->inBooleanMode();
Ordering the results
If no order is specified, the results will be ordered by score (most relevant first). But you can also order the results by any column of your table.
$builder->orderBy('post_status', 'asc')->orderByScore(); // "post_status" is a column of the original table
In the example below, the results will be ordered by status first, and then by descending score.
Filters, tokenizer, stopwords and stemming
In your config file, you can customize the way the indexed content and search terms will be processed:
return [ // ... 'sqlout' => [ // ... 'filters' => [ // anything callable (function name, closure...) 'strip_tags', 'html_entity_decode', 'mb_strtolower', 'strip_punctuation', // this helper is provided by Sqlout (see helpers.php) ], 'token_delimiter' => '/[\s]+/', 'minimum_length' => 2, 'stopwords' => [ 'est', 'les', ], 'stemmer' => Wamania\Snowball\Stemmer\French::class, ], ];
In the example, the stemmer comes from the package
wamania/php-stemmer
,
but any class with a stem
method, or anything callable such as a closure, will do.
As for stopwords, you can either list them directly in the config (as shown above), or load them from a file. The file can be either a TXT file (with one stopword per line), or a PHP file that returns an array:
// config/scout.php return [ // ... 'sqlout' => [ // ... 'stopwords' => 'storage/app/stopwords/fr.php', // ... ], ];
// storage/app/stopwords/fr.php return [ 'à', 'le', 'la', ];
You may want to use the package voku/stop-words
,
which provides collections of stopwords for various languages as PHP files,
or yooper/stop-words
,
which provides them as TXT files.