(858) 586 7777 | About | Testimonials | Contact
vteams vteams vteams vteams
  • How does it work?
    • Startup Process
    • Your First Day
  • Technologies
    • Hire PHP Developer
    • Hire App Developer
    • Hire JavaScript Developer
    • Hire ROR Developer
    • Hire IOS Developer
    • Hire .NET Developer
    • Hire AI Developer
    • Hire Robotics Engineer
  • Sample Budgets
  • Meet The Team
  • Experiments
  • Captain’s Log
  • Blog
vteams vteams
  • How does it work?
    • Startup Process
    • Your First Day
  • Technologies
    • Hire PHP Developer
    • Hire App Developer
    • Hire JavaScript Developer
    • Hire ROR Developer
    • Hire IOS Developer
    • Hire .NET Developer
    • Hire AI Developer
    • Hire Robotics Engineer
  • Sample Budgets
  • Meet The Team
  • Experiments
  • Captain’s Log
  • Blog
Blog
  1. vteams
  2. Blog
  3. Full Text Search on Multiple Columns using Laravel & MySQL InnoDB
Jun 01
Full Text Search, Laravel

Full Text Search on Multiple Columns using Laravel & MySQL InnoDB

  • June 1, 2020

Are you tired of hearing about slow functioning search databases? Now you can quickly improve search experience with the use of Full text search on multiple columns using Laravel & MySQL InnoDB?

This article aims to provide a full tutorial about the implementation of Full Text Search in Laravel. You can use it in any version of laravel greater than 4.

It is often raised as a concern that Laravel does not support Full-Text searches due to it being too MySQL specific. However, you can still implement it by using RAW queries concerning vendor-specific. Laravel is a powerful tool that provides significant improvement to Full text search.

GREATER CONTROL OVER Full Text  SEARCH

You can add multiple filters in the search database while working on full text search on multiple columns. Furthermore, it allows you to control searches rather than just relying on basic and limited searches. For example:

  • A full-text index in MySQL is an index of type FULLTEXT.
  • Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes are created only for CHAR, VARCHAR, or TEXT columns.
  • A FULLTEXT index definition is given in the CREATE TABLE statement when a table is created or added later using ALTER TABLE or CREATE INDEX.
  • For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that. You can load data into a table that has an existing FULLTEXT index.

How do you implement Full Text Search in Laravel?

If you are not using MySQL 5.6+ we must set the Database Engine to MyISAM instead of InnoDB.

Here is the Migration code for posts table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
<?php
 
use Illuminate\Database\Migrations\Migration;
 
use Illuminate\Database\Schema\Blueprint;
 
class CreatePostsTable extends Migration {
 
        /**
 
Run the migrations.
 
@return void
 
/
 
        public function up()
 
        {
 
               Schema::create('posts', function(Blueprint $table) {
 
                       $table->increments('id');
 
                       $table->string('title');
 
                       $table->text('body');
 
                       $table->timestamps();
 
               });
 
 
 
               DB::statement('ALTER TABLE posts ADD FULLTEXT search(title, body)');
 
        }
 
        /**
 
Reverse the migrations.
 
@return void
 
/
 
        public function down()
 
        {
 
               Schema::table('posts', function($table) {
 
                $table->dropIndex('search');
 
               });
 
               Schema::drop('posts');
 
        }
 
}

You can also use this statement any time after table creation also.

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

You can also easily alter the table directly in MySQL. As you can observe in the above code that we are creating an index on two columns title & body. The sequence of columns should be the same when using in a raw query (explaining in next) in Laravel.

QUERY CALL IN CONTROLLER

Now you can use the below query to call in table using

1
SELECT * FROM `posts` WHERE MATCH(title,body) AGAINST('testing post' IN BOOLEAN MODE);

or

1
Post::whereRaw('MATCH (title, body) AGAINST (?)' , array($search))->get();

Use the following when you want to query the exact phrase in the database:

1
SELECT * FROM `posts` WHERE MATCH(title,body) AGAINST('"testing post"' IN BOOLEAN MODE);

OPTIONAL SEARCH MODIFIERS

The optional search_modifier allows you to specify the search type and multiple search functions. It can be any of the following values:

1
2
3
4
5
6
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
 

The default mode is IN NATURAL LANGUAGE MODE.

RETURN THE RELEVANCE SCORE

Whenever you use the MATCH() function, each row in the table is assigned a relevance value. In other words, each row gets a score that determines how relevant it is to the search term.

The results are shown in order by the relevance factor(highest relevance rfirst). It is noted that relevance values are nonnegative floating-point numbers. Zero relevance means no similarity.

Relevance is computed based on the number of words in the row (document), the number of unique words in the row, the total number of words in the collection, and the number of rows that contain a particular word.

To return the relevance of each result, simply include the MATCH() function in your list of columns to select.

1
2
3
4
5
6
SELECT
  MATCH(title,body) AGAINST('Iron Maiden') AS Relevance,
  title,
  body
FROM posts
WHERE MATCH(title,body) AGAINST('Iron Maiden');

RESULTS ABOVE A CERTAIN RELEVANCE SCORE

In this case, Its is to specify that the relevance score must be higher than 2.

1
2
3
4
5
6
SELECT
  MATCH(title,body) AGAINST('Iron Maiden') AS Relevance,
  title,
  body
FROM posts
WHERE MATCH(title, body) AGAINST('Saga of a Moose') > 2;

 This was an attempt to show you a simple tutorial concerning the implementation of Full Text search in Laravel. Now you can work on multiple tables, columns, and improve your search filters within the search database.

But, If you still have any confusion or want our help with it. You can directly talk to us here.

  • Facebook
  • Twitter
  • Tumblr
  • Pinterest
  • Google+
  • LinkedIn
  • E-Mail

Comments are closed.

SEARCH BLOG

Categories

  • Blog (490)
  • Captain's Log (1)
  • Closure Reports (45)
  • Experiments (7)
  • How-To (56)
  • Implementation Notes (148)
  • Learn More (156)
  • LMS (8)
  • Look Inside (10)
  • Operations Log (12)
  • Programmer Notes (20)
  • R&D (14)
  • Rescue Log (4)
  • Testimonials (25)
  • Uncategorized (4)

RECENT STORIES

  • GitHub Actions- Automate your software workflows with excellence
  • Yii Framework – Accomplish Repetitive & Iterative Projects with Ease
  • A Recipe for CRM Software Development
  • Are Agile and DevOps the same?
  • The Data Scientist’s Toolset

ARCHIVES

In Short

With the vteams model, you bypass the middleman and hire your own offshore engineers - they work exclusively for you. You pay a reasonable monthly wage and get the job done without hassles, re-negotiations, feature counts or budget overruns.

Goals for 2020

  • Open development center in Australia
  • Complete and Launch the Robot
  • Structural changes to better address Clients' needs

Contact Us

Address: NEXTWERK INC.
6790 Embarcadero Ln, Ste 100,
Carlsbad, CA 92011, USA

Tel: (858) 586 7777
Email: fahad@nextwerk.com
Web: www.vteams.com

© 2020 vteams. All Rights Reserved.

Content Protection by DMCA.com