How can I make a full text index of the column?

Question

Here is my current migration:

class News extends Migration
{
    public function up()
    {
        Schema::create('News', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->text('description');
            $table->integer('user_id')->unsigned()->index();
            $table->string('imgPath')->nullable();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::drop('News');
    }
}

Now I need to make a full text index on these columns separately: title description. So I'm looking for something like this: ->fulltext(). But I don't find anything similar in the Laravel documentation.

Anyway,

1. how can I make a full text index on a single column in migration? Like: (title)
2. Also for my information, how can I make a composite full text index on multiple columns in migration? Like: (title, description)

---

Note: I want an intex that lets me search like this: . . . match(col) against('value')

Answer 1

Laravel doesn't support FULLTEXT search. But you can use raw queries as:

DB::statement('ALTER TABLE News ADD FULLTEXT search(title, description)');

Note - that if you are not using MySQL 5.6+ we must set the Database Engine to MyISAM instead of InnoDB.

$table->engine = 'MyISAM'; // means you can't use foreign key constraints

For searching, you can do as:

$q = Input::get('query');

->whereRaw("MATCH(title,description) AGAINST(? IN BOOLEAN MODE)", array($q))

Answer 2

Be aware! The answer before is correct but if you're testing your app and you are using sqlite (memory) this command will fail! Check the database type

if( getenv('DB_CONNECTION') === 'mysql' ) // or something else

Answer 3

$table->fulltext(['title', 'description']); you can also use it like above https://laravel.com/docs/8.x/migrations#creating-indexes

Answer 4

Laravel 9+ now fully supports full text index. To define a full index column use the below

$table->fullText('body');

And you can also use the whereFullText in your query

User::whereFullText->('body', 'john doe')->get();

Note: Laravel 8+ support the fullText index but there's no option for Where clause

Answer 5

Since Laravel release 8.79 full text search was implemented for PostgreSQL and MySQL Pull Request on GitHub "[8.x] Implement Full-Text Search for MySQL & PostgreSQL"

So, now you can easily not just add an fulltext index to a column but perform a query on it with Eloquent:

public function up()
{
    Schema::create('news', function (Blueprint $table) {
        // ...
        $table->string('title');
        $table->text('description');
        // ...
        $table->fullText(['title', 'description'])->language('english');
    });
}

And you of course can use your index to execute some queries using Eloquent:

News::whereFullText->(['title', 'description'], 'YOUR_QUERY_TEXT')->get();

Also check the documentation: Laravel 8.x - Available index types