Tutorial: Full Text Search on Laravel 5

MySQL provides a powerful mechanism for searching text across multiple columns. This feature is called Full Text Search and was reserved for tables of type MyISAM. Luckily enough, it has been added to tables of type InnoDB with the release of MySQL version 5.6+.

Full Text Search allows searching text efficiently accross multiple columns. It has several search modes that accept different matching patterns. When using it, a relevance score is calculated, and the results are automatically ordered by it.

Is it missing from Laravel?

Yes, Taylor Otwell, the creator of Laravel, wants to keep Eloquent as platform-independent as possible. And since Full Text Search is specific to MySQL, it was not included. In his own words: “Full text search is pretty vendor specific and not something that is currently supported by Laravel.” Read Thread

On this tutorial, I will cover how to easily implement full text searches.

Replacement for Laravel Scout / Algolia?

Laravel Scout is a powerful tool to do Full Search Text and Algolia is one of the engines supported. In my opinion, there are not as simple to set up as it seems, and for many projects, it seems a little overkill. Also, Algolia is a paid and external service which is not as convenient for many.

When to use Full Text Search?

  • To search on multiple columns at once. i.e. matching users against first name, last name, email, and username.
  • To sort results by relevance. i.e. closer matches should be at the top
  • To improve the performance of queries using `LIKE ‘%term%’
  • To apply advanced search patterns like skipping results that include a specific word.
  • To use your own MySQL server

Tutorial: Implementing Full Text Search in Laravel 5

In this tutorial, we will use full text search to find users by matching any of the columns first name, last name, and email. We will develop a reusable trait to create a query scope that can be used along with query builders to fine-tune the search.

1) Adding the index on the migration

FULL TEXT is a type of index and it accepts one or more columns. Since Laravel does not ship with a method for that, we will need to use a raw statement.

Remember to use the Illuminate\Support\Facades\DB

2) Run the migrations

php artisan migrate

Remember that you need MySQL 5.6+ if using InnoDB

3) Defining the searchable columns

Go to the User model and add the following code. It is required to put exactly the same columns that were defined in the index in step 1.

If these columns do not match the columns defined when creating the index, then MySQL will not find the index and it will throw the following error message: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list

4) Creating the trait

Instead of putting the search code on every model, we will use a trait to centralize it and avoid repetition.

Create the file FullTextSearch.php on the same folder as your models and paste the following code.

Default Mode

MATCH (first_name, last_name, email) AGAINST ('john')

This will match any column that has a word that is exctly ‘john’.

Boolean Mode

There are several search modes, but we will use the boolean mode which allows the use of special operators to fine-tune the search. In particular, we will use the * as a wildcard. For a full list of operators visit the official documentation.

MATCH (first_name, last_name, email) AGAINST ('john*' IN BOOLEAN MODE)

This will match any column that has a word that starts with ‘john’ such as ‘johny’.

5) Include the trait

Back to the User model, simply include the trait.

6) Start searching

Using the search is really simple. Since we used a query scope it can be used alone, or in conjunction with other where clauses, order by, or even pagination.

7) Using on multiple models

Well, we already have the trait, all you need to do in order to use it with any other model is to:

  1. Add the Full Text index
  2. Run the migration
  3. Define the searchable columns
  4. Import the FullTextSearch trait

Conclusion

I hope this tutorial was useful. In my opinion, setting up this feature in Laravel with MySQL is very simple and powerful. It is definitely worth a try before going to the other more complex solutions.

Pro Tip: Sorting by Relevance Score

To sort the results by relevance. You may select the auto-generated relevance score as a column in MySQL like this:

Simply replace the scopeSearch method in the FullTextSearch trait:

 

Share Post :