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

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