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 :

30 Comments

  • Dang Quoc Hoi
    February 2, 2018 at 6:34 am 

    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
      February 2, 2018 at 8:47 am 

      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.

  • Dichra
    February 9, 2018 at 11:20 am 

    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
      February 9, 2018 at 1:54 pm 

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

  • Marc De Gagné
    March 10, 2018 at 1:31 pm 

    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
      March 10, 2018 at 5:26 pm 

      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é
        March 11, 2018 at 10:53 am 

        Merci beaucoup!

  • Vladimir Igonin
    March 15, 2018 at 11:37 am 

    Thank you! This is very useful article!

  • Aaron Humphreys
    March 15, 2018 at 10:27 pm 

    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
      March 15, 2018 at 10:30 pm 

      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
        March 16, 2018 at 7:54 am 

        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
    April 9, 2018 at 3:46 am 

    Thanks Arian for this tutorial. It is very useful.

    • Arian Acosta
      July 28, 2018 at 9:07 am 

      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
        July 28, 2018 at 1:05 pm 

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

  • Monkeyphan
    September 7, 2018 at 4:39 am 

    thanks man

  • Victor Otávio
    October 8, 2018 at 8:37 am 

    This post save my life. Thanks man!

  • Juan José Ruiz Muñoz
    October 24, 2018 at 2:31 pm 

    Awesome. Thanks so much!

  • Dhanesh Malviya
    October 28, 2018 at 10:48 pm 

    Thanks!..nice tutorial..

  • Eduardo França
    December 30, 2018 at 12:42 am 

    The more you know… up to this day I thought MySQL only supported text search with the slow like %something% operator. Thanks for opening my eyes.

  • Freddy Delgado
    May 22, 2019 at 8:55 am 

    Great Solution!!!!

  • Alejandro Iván
    June 1, 2019 at 10:22 pm 

    Hi Arian!
    For the sorted by relevance, the selectRaw has to include the data along with the MATCH(), so this:
    return $query->selectRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE) AS relevance_score", [$searchableTerm])        ->whereRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)", $searchableTerm)        ->orderByDesc('relevance_score');

    Should probably be this:
    return $query->selectRaw("*, MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE) AS relevance_score", [$searchableTerm])->whereRaw("MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)", $searchableTerm)->orderByDesc('relevance_score');

    • Mahmoud Ali Kassem
      July 29, 2019 at 12:07 pm 

      I was only getting relevance_score before applying your edit, but after I did that, it bring all record and I have no control on the return data.

      • Mahmoud Ali Kassem
        July 29, 2019 at 12:08 pm 

        I think it need something to bring the required data only to reduce the request size

        • Mahmoud Ali Kassem
          July 29, 2019 at 12:13 pm 

          I did the following and it worked for me, but only if table id is id, so user_id or post_id needs a better solution:
          return $query->selectRaw(“id,{$columns}, MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE) AS relevance_score”, [$searchableTerm])
          ->whereRaw(“MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)”, $searchableTerm)
          ->orderByDesc(‘relevance_score’);

  • Hazem Ha
    June 3, 2019 at 7:47 am 

    Big Thanks

  • Sutapa Mondal
    August 1, 2019 at 4:02 am 

    Hello Arian,
    Brilliant text search blog on Laravel, I must say. Keep it up! Also I would love to suggest all readers to go through Best Framework for further knowledge on Laravel.

Leave a Reply