Full text search for multiple words using Laravel and MySQL
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
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();
php mysql laravel
edited May 25 at 18:40
200_success
123k14143399
asked May 25 at 17:53
derrickrozay
533
add a comment |Â
up vote
2
down vote
favorite
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();
php mysql laravel
edited May 25 at 18:40
200_success
123k14143399
asked May 25 at 17:53
derrickrozay
533
What do you want to improve? Speed, looks?
– Kyslik
May 31 at 7:51
@Kyslik Both if possible, if not I want to optimize for performance
– derrickrozay
May 31 at 14:22
To be frank usinglikeis 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.
– Kyslik
Jun 3 at 7:53
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
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();
php mysql laravel
edited May 25 at 18:40
200_success
123k14143399
asked May 25 at 17:53
derrickrozay
533
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();
php mysql laravel
edited May 25 at 18:40
200_success
123k14143399
asked May 25 at 17:53
derrickrozay
533
edited May 25 at 18:40
200_success
123k14143399
edited May 25 at 18:40
200_success
123k14143399
edited May 25 at 18:40
200_success
123k14143399
123k14143399
asked May 25 at 17:53
derrickrozay
533
asked May 25 at 17:53
derrickrozay
533
asked May 25 at 17:53
derrickrozay
533
533
What do you want to improve? Speed, looks?
– Kyslik
May 31 at 7:51
@Kyslik Both if possible, if not I want to optimize for performance
– derrickrozay
May 31 at 14:22
To be frank usinglikeis 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.
– Kyslik
Jun 3 at 7:53
add a comment |Â
What do you want to improve? Speed, looks?
– Kyslik
May 31 at 7:51
@Kyslik Both if possible, if not I want to optimize for performance
– derrickrozay
May 31 at 14:22
To be frank usinglikeis 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.
– Kyslik
Jun 3 at 7:53
What do you want to improve? Speed, looks?
– Kyslik
May 31 at 7:51
What do you want to improve? Speed, looks?
– Kyslik
May 31 at 7:51
@Kyslik Both if possible, if not I want to optimize for performance
– derrickrozay
May 31 at 14:22
@Kyslik Both if possible, if not I want to optimize for performance
– derrickrozay
May 31 at 14:22
To be frank using
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.– Kyslik
Jun 3 at 7:53
To be frank using
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.– Kyslik
Jun 3 at 7:53
add a comment |Â
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f195175%2ffull-text-search-for-multiple-words-using-laravel-and-mysql%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
What do you want to improve? Speed, looks?
– Kyslik
May 31 at 7:51
@Kyslik Both if possible, if not I want to optimize for performance
– derrickrozay
May 31 at 14:22
To be frank using
likeis 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.– Kyslik
Jun 3 at 7:53