Are you tired of hearing about slow functioning search databases? Now you can quickly improve search experience with the use of Full text search on multiple columns using Laravel & MySQL InnoDB?
This article aims to provide a full tutorial about the implementation of Full Text Search in Laravel. You can use it in any version of laravel greater than 4.
It is often raised as a concern that Laravel does not support Full-Text searches due to it being too MySQL specific. However, you can still implement it by using RAW queries concerning vendor-specific. Laravel is a powerful tool that provides significant improvement to Full text search.
GREATER CONTROL OVER Full Text SEARCH
You can add multiple filters in the search database while working on full text search on multiple columns. Furthermore, it allows you to control searches rather than just relying on basic and limited searches. For example:
- A full-text index in MySQL is an index of type FULLTEXT.
- Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes are created only for CHAR, VARCHAR, or TEXT columns.
- A FULLTEXT index definition is given in the CREATE TABLE statement when a table is created or added later using ALTER TABLE or CREATE INDEX.
- For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that. You can load data into a table that has an existing FULLTEXT index.
How do you implement Full Text Search in Laravel?
If you are not using MySQL 5.6+ we must set the Database Engine to MyISAM instead of InnoDB.
Here is the Migration code for posts table:
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 53 54 55 56 57 58 59 60 61 | <?php use Illuminate\Database\Migrations\Migration; use Illuminate\Database\Schema\Blueprint; class CreatePostsTable extends Migration { /** Run the migrations. @return void / public function up() { Schema::create('posts', function(Blueprint $table) { $table->increments('id'); $table->string('title'); $table->text('body'); $table->timestamps(); }); DB::statement('ALTER TABLE posts ADD FULLTEXT search(title, body)'); } /** Reverse the migrations. @return void / public function down() { Schema::table('posts', function($table) { $table->dropIndex('search'); }); Schema::drop('posts'); } } |
You can also use this statement any time after table creation also.
1 | DB::statement('ALTER TABLE posts ADD FULLTEXT search(title, body)'); |
You can also easily alter the table directly in MySQL. As you can observe in the above code that we are creating an index on two columns title & body. The sequence of columns should be the same when using in a raw query (explaining in next) in Laravel.
QUERY CALL IN CONTROLLER
Now you can use the below query to call in table using
1 | SELECT * FROM `posts` WHERE MATCH(title,body) AGAINST('testing post' IN BOOLEAN MODE); |
or
1 | Post::whereRaw('MATCH (title, body) AGAINST (?)' , array($search))->get(); |
Use the following when you want to query the exact phrase in the database:
1 | SELECT * FROM `posts` WHERE MATCH(title,body) AGAINST('"testing post"' IN BOOLEAN MODE); |
OPTIONAL SEARCH MODIFIERS
The optional search_modifier allows you to specify the search type and multiple search functions. It can be any of the following values:
1 2 3 4 5 6 | { IN NATURAL LANGUAGE MODE | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION | IN BOOLEAN MODE | WITH QUERY EXPANSION |
The default mode is IN NATURAL LANGUAGE MODE.
RETURN THE RELEVANCE SCORE
Whenever you use the MATCH() function, each row in the table is assigned a relevance value. In other words, each row gets a score that determines how relevant it is to the search term.
The results are shown in order by the relevance factor(highest relevance rfirst). It is noted that relevance values are nonnegative floating-point numbers. Zero relevance means no similarity.
Relevance is computed based on the number of words in the row (document), the number of unique words in the row, the total number of words in the collection, and the number of rows that contain a particular word.
To return the relevance of each result, simply include the MATCH() function in your list of columns to select.
1 2 3 4 5 6 | SELECT MATCH(title,body) AGAINST('Iron Maiden') AS Relevance, title, body FROM posts WHERE MATCH(title,body) AGAINST('Iron Maiden'); |
RESULTS ABOVE A CERTAIN RELEVANCE SCORE
In this case, Its is to specify that the relevance score must be higher than 2.
1 2 3 4 5 6 | SELECT MATCH(title,body) AGAINST('Iron Maiden') AS Relevance, title, body FROM posts WHERE MATCH(title, body) AGAINST('Saga of a Moose') > 2; |
This was an attempt to show you a simple tutorial concerning the implementation of Full Text search in Laravel. Now you can work on multiple tables, columns, and improve your search filters within the search database.
But, If you still have any confusion or want our help with it. You can directly talk to us here.
Comments are closed.