Explore fulltext search in laravel 10, 11 with Relations

While using LIKE operator in laravel can basically get the job done in Laravel when working on simple applications with small datasets Fulltext allows you to perform efficient searches on large text-based data. It is particularly useful when you need to search for specific words or phrases within large text fields, such as articles, descriptions, or comments.

Using fullText search in Laravel

Take a scenario where you want users to be able to search product names and description using Natural Language. In Laravel we need to:

Specify FullText a migration:

FullText indexes can be created with fullText() which takes the column(s), index name and algorithm type

public function up(): void
{
    Schema::create('products', function (Blueprint $table) {
        $table->ulid('id')->primary();
        $table->string('name');
        $table->longText('description');
        $table->fullText([
                'name',
                'description',
            ], 'product_full_text_search');
        $table->softDeletes();
        $table->timestamps();
    });
}

Single column can be index using:

$table->fullText("name");

and multiple columns can be specified using an array in place of the string.
The second which is the index name is optional as laravel uses the “table_name_column_names_fulltext” as title e.g “products_name_description_fulltext”, so also is the third paramter which is the algorithm type. Boolean Fulltext Search is adopted as the default in MySQL

Dropping FullText

$table->dropFullText("products_name_description_fulltext");

Using FullText in a WHERE clause

Product::query()->whereFullText(['name', 'description], $request->query('query'))->get();

The whereFullText() takes column(s) as first parameter and the search string as the next parameter.

FullText Search on relationship

This is quite complicated compared to a search on the model.

Product::with(['productCategory', 'productType', 'productImages'])
    ->where(function ($query) use ($request) {
        $query->whereFullText('name', $request->get('query'));
        $query->orWhereHas('productCategory', function ($categoryQuery) use ($request) {
            $categoryQuery->whereFullText(['name'], $request->get('query'));
        });
        $query->orWhereHas('productType', function ($productType) use ($request) {
            $productType->whereFullText(['name'], $request->get('query'));
        });
    })

The query above search for a match on the current Model Product and its associated model ProductCategory and ProductType using combination of where and whereFullText method.

Note: Remember to add the required relationship in the model as needed.

Conclusion:

Fulltext search is quite useful when performing string searches, but its limitations become apparent when there’s a need to search data or integer columns in the database. However, this is acceptable as fulltext search was never intended for such usage. Nowadays, premium search engines like Elasticsearch and Algolia are commonly used, and they also offer AI features.

Additionally, using fulltext search on small datasets can be considered over-engineering, and as the data grows, performance enhancements may be required. For advanced usage, developers might need to resort to using raw SQL queries to configure parsers and relevance rankings.

It’s important to note that there is also the orWhereFulltext() method available in Laravel for constructing fulltext search queries.