In today's data-driven world, efficient search functionality is crucial for delivering a seamless user experience. Laravel combined with MySQL's full-text search capabilities, can empower you to build robust search functionalities for your web applications. In this blog post, we'll explore how to harness the power of MySQL's full-text search in conjunction with Laravel and its elegant ORM, Eloquent.

Understanding MySQL full-text search

Before diving into Laravel and Eloquent, let's grasp the basics of MySQL's full-text search.

MySQL's full-text search is designed for searching text content efficiently. Unlike regular SQL queries, which match exact words, full-text search enables you to find relevant results based on natural language patterns and relevance scores. It's an excellent choice for implementing search functionality in applications.

Creating a model and migration

Let's start with creating a model and migration for the table you want to enable full-text search on. For this example, let's assume we have a posts table for a blog application.

1php artisan make:model Post -m

In the generated migration file, modify the up method to include the columns you want to search within. For instance, you might want to search the title and content columns:

 1return new class() extends Migration {
 2    public function up(): void
 3    {
 4        Schema::create('posts', function (Blueprint $table) {
 5            $table->id();
 6            $table->string('title');
 7            $table->text('content');
 8            $table->timestamps();
 9        });
10
11        // Add a full-text index
12        DB::statement('ALTER TABLE posts ADD FULLTEXT posts_fulltext (title, content)');
13    }
14
15    public function down(): void
16    {
17        Schema::table('posts', function (Blueprint $table) {
18            $table->dropIndex('posts_fulltext');
19        });
20    }
21}

We also provide a down implementation to drop the full-text index when the migration is rolled back.

Migrate your database to create the posts table and the full-text index:

1php artisan migrate

Implementing full-text search with Eloquent

With the database set up, let's use Eloquent to perform full-text searches. Open your Post model (app/Models/Post.php) and add the following method:

1public function search($query)
2{
3    return self::whereRaw("MATCH(title, content) AGAINST(? IN BOOLEAN MODE)", [$query])
4        ->get();
5}

This method uses the MATCH ... AGAINST syntax to search for rows where the title or content columns match the given query.

Performing full-text searches

Now that you've set up your model, you can perform full-text searches in your Laravel application. Here's an example of how to use it in a controller:

1use App\Models\Post;
2
3public function search(Request $request)
4{
5    $query = $request->input('q');
6    $results = Post::search($query);
7
8    return view('search-results', ['results' => $results]);
9}

In this example, we retrieve the search query from the request and call the search method on the Post model. The results can then be displayed in a view.

The different matching modes

MySQL's full-text search provides several modes for matching and searching text content. These modes affect how the search query matches words and phrases in the text. Let's explore the different modes available for MySQL full-text matching:

1. Natural Language Mode (IN NATURAL LANGUAGE MODE): This is the default mode for MySQL full-text search. In this mode, MySQL attempts to find the most relevant results based on natural language patterns. It performs stemming (reducing words to their root form) and stopword filtering (removing common words like "and," "the," "is," etc.). This mode is suitable for general-purpose search and is often used when you want to provide user-friendly, natural language search queries.

   1MATCH(title, content) AGAINST('programming books' IN NATURAL LANGUAGE MODE)
   

2. Boolean Mode (IN BOOLEAN MODE): In this mode, MySQL treats the search query as a boolean expression. You can use operators like + (AND), - (NOT), * (wildcard), and " (phrase) to create complex search queries. Boolean mode provides more fine-grained control over search results but requires users to use specific syntax for their queries.

   1MATCH(title, content) AGAINST('+programming -books' IN BOOLEAN MODE)
   

3. Query Expansion Mode (WITH QUERY EXPANSION): This mode is an extension of the natural language mode. It not only finds results matching the original query but also expands the search by including synonyms from a thesaurus file. This can help in finding relevant results even if the original query doesn't contain the exact terms used in the documents.

   1MATCH(title, content) AGAINST('programming' WITH QUERY EXPANSION)
   

4. Word Search Mode (IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION): This mode combines the benefits of both natural language mode and query expansion. It performs a natural language search but also includes query expansion to broaden the search scope.

   1MATCH(title, content) AGAINST('programming' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)
   

5. Minimum Word Length (ft_min_word_len): MySQL's full-text search has a minimum word length setting (usually configured in the MySQL server) that determines the minimum length of words to be indexed. Words shorter than this length are typically ignored in the index. You may need to adjust this setting to include shorter words in your search if necessary.

Keep in mind that the choice of search mode depends on the specific requirements of your application. Natural language mode is the most user-friendly but might not be suitable for all scenarios. Boolean mode offers more control but requires users to understand the syntax. Query expansion modes can be helpful when you want to improve the scope of search results. Experiment with these modes to find the one that best fits your application's needs.

Moving up to a dedicated search engine

While MySQL's full-text index provides a solid foundation for implementing search functionality in your Laravel application, there are scenarios where it may not be sufficient. For example, as your application scales and the volume of data grows significantly, the performance of full-text searches might start to degrade.

Additionally, MySQL's full-text search is primarily designed for basic text matching and relevance scoring. If you require advanced features such as typo tolerance, geospatial search, or real-time indexing, it may be time to consider transitioning to a dedicated search server like Algolia or Meilisearch.

Laravel Scout, a powerful package for Laravel, simplifies this transition by offering an elegant way to integrate these external search services. Algolia, for instance, excels in providing lightning-fast search experiences with features like typo-tolerance and geolocation-based search, making it an excellent choice for applications demanding top-notch search performance and user experience. These solutions come at a cost though, so it's important to weigh the pros and cons before making a decision.

Conclusion

In this blog post, we've explored how to integrate MySQL's full-text search capabilities with Laravel and Eloquent to create powerful search functionality for your web applications. By setting up the database, creating a model, and using the MATCH ... AGAINST syntax, you can efficiently search and retrieve relevant content.