1. Add an index in migration
Fulltext is an index type that accepts 1 or more columns. Because Laravel doesn’t have a function that supports fulltext indexing, we have to write it ourselves.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | public function up() { Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('email')->unique(); $table->string('password'); $table->rememberToken(); $table->timestamps(); DB::statement('ALTER TABLE users ADD FULLTEXT `search` (`email`, `name`)'); DB::statement('ALTER TABLE users ENGINE = MyISAM'); }); } |
Here we use the Storage Engine MyISAM instead of InnoDB
2. Define a searchable field Go to the User model and add the following code. We need to add the correct column name defined in step 1.
1 2 3 4 5 | protected $searchable = [ 'email', 'name' ]; |
If the column names are not defined correctly, MySql will not be able to find the index and will return an SQLSTATE [HY000] error: General error: 1191 Can’t find FULLTEXT index matching the column list.
3. Create Trait
In a project, there is not only one model to search. Instead of writing code search in all those models, we’ll create a trait to avoid code duplication. Create Traits folder in app and fulltextsearch.php file
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | <?php namespace AppTraits; trait FullTextSearch { /** * Replaces spaces with full text search wildcards * * @param string $term * @return string */ protected function fullTextWildcards($term) { // removing symbols used by MySQL $reservedSymbols = ['-', '+', '<', '>', '@', '(', ')', '~']; $term = str_replace($reservedSymbols, '', $term); $words = explode(' ', $term); foreach ($words as $key => $word) { /* * applying + operator (required word) only big words * because smaller ones are not indexed by mysql */ if (strlen($word) >= 2) { $words[$key] = '+' . $word . '*'; } } $searchTerm = implode(' ', $words); return $searchTerm; } /** * Scope a query that matches a full text search of term. * * @param IlluminateDatabaseEloquentBuilder $query * @param string $term * @return IlluminateDatabaseEloquentBuilder */ public function scopeSearch($query, $term) { $columns = implode(',', $this->searchable); $query->whereRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)", $this->fullTextWildcards($term)); return $query; } } |
4. Config
-Because MySql does not index words with small characters (the default is 4), we will have to change the ft_min_word_len field below the [mysqld] line in my.cnf file. on ubuntu: edit the file in /etc/mysql/my.cnf. If you use XAMPP then edit it in the [xampp installation directory] /etc/mysql/my.cnf
1 2 3 | ft_min_word_len = 2 //nếu dùng MyISAM innodb_ft_min_token_size=2 //nếu dùng InnoDB |
-Also, if the search term is stopword, MySQL will ignore the word. If you want to remove the stopword, add the line ft_stopword_file = “” into the my.cnf file.
1 2 | ft_stopword_file = "" |
5. Use
1 2 3 4 5 6 7 8 9 10 | User::search('arian acosta')->get(); User::search('acosta arian')->get(); User::search('ari aco')->get(); User::search('arian')->get(); User::search('arian acosta somethingelse')->get(); User::search('arian')->where('is_active', '=', 1)->get(); User::search('arian')->latest()->get(); User::search('arian')->paginate(); |
This article I translated from page https://arianacosta.com/php/laravel/tutorial-full-text-search-laravel-5/