CODEWITHSUNDEEP
mysqllaravelfull-text-search
Dharmesh Rakholia
Dharmesh Rakholia

Reputation: 1238

FullText search in laravel 5

I have query regarding fulltext search in laravel.

I applied index in various columns.

I have one keyword

bar soap

now i need record in following sequence.

My query is here

select `inventories`.*, MATCH (title, short_description, brand, long_description, inventory_code, product_code) AGAINST ('bar soap' IN BOOLEAN MODE) as score from `inventories` where MATCH (title, short_description, brand, long_description, inventory_code, product_code) AGAINST ('bar soap' IN BOOLEAN MODE) order by `score` desc

I have also review this doc.

Your help would be appreciate.

Upvotes: 0

Views: 1117

Answers (2)

Dilip Hirapara
Dilip Hirapara

Reputation: 15296

InnoDB tables require a FULLTEXT index on all columns of the MATCH() so first give FULLTEXT index

Run this query:

  ALTER TABLE `inventories` ADD FULLTEXT(`title`,`short_description`,`brand`,`long_description`,`inventory_code`,`product_code`);

Then you are able to run full-text search:

SELECT * FROM inventories 
WHERE MATCH(title, short_description, brand, long_description, inventory_code, product_code)
AGAINST('bar soap' IN NATURAL LANGUAGE MODE)

Upvotes: 1

kevinp1005
kevinp1005

Reputation: 21

Try something like this :

    // Text
    $search = 'bar soap';
    $parsedSearch = explode(' ', 'bar soap');

    // Create pattern
    $patterns = [];
    $patterns[] = $search; // bar soap
    $patterns[] = $parsedSearch[1] . ' ' . $parsedSearch[0]; // soap bar
    $patterns[] = $parsedSearch[0]; //bar
    $patterns[] = $parsedSearch[1]; //soap


    // Set Query
    $query = "select `inventories`.*, MATCH (title, short_description, brand, long_description, inventory_code, product_code) AGAINST ('bar soap' IN BOOLEAN MODE) as score from `inventories` where MATCH (title, short_description, brand, long_description, inventory_code, product_code) AGAINST ('bar soap' IN BOOLEAN MODE) order by field(`score`," . implode(',', $patterns) . ")";

    return $query;

You might want to create a function to create the pattern to support other search and then use MySQL ORDER BY FIELD function to sort it.

Upvotes: 1

Related Questions