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 type in "cam handle" it wouldn't return results that were "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();
like
is not full text search, google around to see what full text search is, key for the performance part is correct indexing - it all depends on how much data you have. \$\endgroup\$ – Kyslik Jun 3 '18 at 7:53