Implement FullText search in Laravel 5.4

You are working on a Laravel project?
And you need fulltext search and can’t of won’t set up apache solr or elasticsearch for that?
So did I and in this post I will go show you how I did it. How I implemented a fulltext search in laravel 5.4 and mysql. First let’s say a bit more about fulltext.

InnoDB FULLTEXT Indexes

FULLTEXT indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns) to help speed up queries. A FULLTEXT index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX. Fulltext search is performed using MATCH() … AGAINST syntax.

That’s what they say on the official site. Basically, if you want a good performing and fast search on mysql you need to create a fulltext index.

If you want to find out more about fulltext index you can read here.

How to implement FullText search in Laravel

First, you need to add a column to your table with the text you want to make the search for. In my case it was post body, title, author, tags, categories.. You can do it directly on the database or with a laravel migration in the up() function. And in the down () function, we first drop the FULLTEXT index and then drop the table. On this project I did db first approach, but this migration should do just fine.

Example:

public function up() 
{
 Schema::create('posts', function(Blueprint $table) {
  // Means you can't use foreign key constraints
 $table->engine = 'MyISAM'; 
			$table->increments('id');
			$table->string('title');
			$table->text('body');
			$table->timestamps();
 });

 DB::statement('ALTER TABLE posts ADD FULLTEXT search(title, body)');
}

public function down()
{
 Schema::table('posts', function($table) {
  $table->dropIndex('search');
 });
 Schema::drop('posts');
}

Search function in the controller

After we created the index we need to add the search functionality in our controller. I used query as a parameter for SEO reasons, but you can do it using a POST request. I named the function search because i had only one search in my project.

public function search($query = null)
{
    if (!$query) {
        abort(404);
    }
    $match  = "MATCH(`text_col`) AGAINST (?)";
    $posts = Post::whereRaw($match, array($query))
             ->orderByRaw($match . ' DESC', array($query))
             ->paginate(12);
        
    if (!$posts || $posts->count() < 1) {
        abort(404);
    }
return view('search')->with(['posts' => $posts, 'query' => $query]);
}

Conclusion

That’s how I did it and it serves me well. 😉

Leave a Reply

Your email address will not be published. Required fields are marked *