Full text search for multiple words using Laravel and MySQL

Question

I'm using Laravel 5.6 and MySQL. I need to search multiple columns for a single word or multiple words (e.g., "camhandle", "cam handle").

The way I was originally doing it was just using column like %$request->keyword% but if I typed in "cam handle" it wasn't returning results matching "camhandle". So I had to search word by word. I came up with this solution but it's messy.

How can I improve this code?

$s = preg_split('/\s+/', $request->keyword, -1, PREG_SPLIT_NO_EMPTY);

$items =   Items
            ::join('ic.itemunits as iu', function($join) {
                $join->on('items.itemcode', '=', 'iu.itemcode');
                $join->on('items.defaultvendorcode', '=', 'iu.vendorcode');
            })
            ->select('items.itemcode', 'items.picturecode', 'items.description')
            ->where(function ($query) use ($s) {
                foreach ($s as $value) {
                    $query->orWhere('items.description', 'like', "%{$value}%");
                    $query->orWhere('items.itemcode', 'like', "%{$value}%");
                    $query->orWhere('iu.itemcode', 'like', "%{$value}%");
                }
            })
            ->get();

Answer 1

Variable naming could be better

The variable name $s is not very descriptive of what it contains. Renaming it to something like $words or $parts would allow anyone reading the code - which includes your future self - to have a clue as to what the variable contains.

Then the loop that uses that:

foreach ($s as $value) {

could be changed to something like:

 foreach ($words as $word) {

This would make it clear that the loop iterates over each word (or part) of the keyword string.

If that variable $s is only used within the anonymous function callback passed to the where() method then it could be moved into that function, which would allow for the elimination of the use statement.

Simplifying JOIN conditions

When calling the query builder's where() method the second argument can be omitted when it is '=':

For example, the following query retrieves users where the value of the votes column is equal to 100 and the value of the age column is greater than 35:

$users = DB::table('users')
            ->where('votes', '=', 100)
            ->where('age', '>', 35)
            ->get();

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method. Laravel will assume you would like to use the = operator:

$users = DB::table('users')->where('votes', 100)->get();

¹.

The same can be done with the on method for joins:

$items =   Items
            ::join('ic.itemunits as iu', function($join) {
                $join->on('items.itemcode', 'iu.itemcode');
                $join->on('items.defaultvendorcode', 'iu.vendorcode');
            })

If the server is using PHP 7.4 or later then an arrow function could be used:

$items =   Items
            ::join('ic.itemunits as iu', fn ($join) =>
                $join->on('items.itemcode', 'iu.itemcode')
                     ->on('items.defaultvendorcode', 'iu.vendorcode')
            )

Answer 2

The way I was originally doing it was just using column like %$request->keyword% but if I typed in "cam handle" it wasn't returning results matching "camhandle". So I had to search word by word.

It may not matter to you anymore, but an alternative would be to do

$keyword_string = str_replace(' ', '%', $request->keyword);

The resulting string column LIKE '%cam%handle%' will match "camhandle". It still will not match "handlebar cam", which may or may not be what you want. You could also switch to REGEXP_LIKE and do

$keyword_string = str_replace(' ', ' ?', $request->keyword);

which will match "cam handle" or "camhandle" but not "camera handle".

I.e. my observation is that for the specific search example you provided, you do not need to search word by word. It may still be better for other searches. For example, "cam handle steel" should probably match "steel camhandle". But without searching word by word, it won't.