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 :

  • Dang Quoc Hoi

    Hi. Thank you for sharing.
    I meet a issue when search email address
    SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected ‘@’, expecting $end (SQL: select count(*) as aggregate from users where MATCH (first_name,last_name,email,address_street,address_city,address_state,address_country,address_postalcode) AGAINST (hh@gmail.com* IN BOOLEAN MODE))

    • Arian Acosta

      Hello Dang! There are a few reserved characters when using the boolean mode in MySQL. I’ve updated the code for the trait, and also added a few improvements. It should work for your use case now. Good luck.

  • http://www.rachid.in Dichra

    Hi Arian! Thanks for this marvelous tutorial.

    Using Laravel Framework version 5.2.45, I had to put $this->fullTextWildcards() in an array in the whereRaw() method, like this :

    $query->whereRaw(“MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)”, [$this->fullTextWildcards($term)]);

    • Arian Acosta

      Hi Dichra! That’s interesting, good to know that! I think I had tested it only in Laravel 5.3+

  • Marc De Gagné

    Hello Arian,

    Great code! It works nicely!
    Excuse my newbeeness to Laravel and to some extent to PHP and MYSQL also.

    I was wondering how to implement the “Pro tip”?
    I tested the SQL directly and it works fine. But how to do it in your FullTextSearch.php?
    I have tried but without any success so far.

    Thanks

    • Arian Acosta

      Hi Marc! I’m glad it was useful. I’ve updated the article to include the code for this. All you need to do is replace the scopeSearch method with the new one. Good luck!

      • Marc De Gagné

        Merci beaucoup!

  • http://igonin.ru Vladimir Igonin

    Thank you! This is very useful article!

  • Aaron Humphreys

    Firstly, thank you very much for this.
    I have an issue though, when implementing the “Sort By Relevance Score”, the query is only returning relevance_score attribute and no other fields from the model. Is this intended? How do we get the entire model back?

    • Aaron Humphreys

      I added select(‘*’) to the beginning of the query to ensure all fields are selected, apparently the raw select was overriding everything else

      return $query->select(‘*’)->selectRaw(“MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE) AS relevance_score”, [$searchableTerm])->whereRaw(“MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)”, $searchableTerm)->orderByDesc(‘relevance_score’);

      • Arian Acosta

        Hi Aaron! I’m glad you found a solution, and thanks for pointing this out. Indeed, selecting the columns is necessary in addition to the relevance score. I would suggest though, to put the select('*') outside the FullTextSearch trait and move it where you are creating the query. This will allow you to select specific columns for each of the models you are searching for. In fact, when searching or listing N rows you should retrieve only the columns that you are going to display, this will improve the performance. Good luck!

  • Tôn Quốc Việt

    Thanks Arian for this tutorial. It is very useful.

    • Arian Acosta

      Hi! This line is important because MySQL full text index does not take into consideration words that have 3 characters or fewer. This makes sure that the operator is only applied to words that are indexed, otherwise the + operator will try to require a word that is not indexed, and this will produce incorrect results. I hope this clarifies your question!

      • Tôn Quốc Việt

        in your code you wrote : strlen($word) >= 3 , that make me confused, I think it should be = 3.

  • http://www.sclrship.com/ Thouhedul Islam Suchi

    Thanks man. You have saved my time.

  • Monkeyphan

    thanks man

  • http://www.victorotavio.com.br/ Victor Otávio

    This post save my life. Thanks man!

  • Juan José Ruiz Muñoz

    Awesome. Thanks so much!

  • Dhanesh Malviya

    Thanks!..nice tutorial..