3
\$\begingroup\$

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();
\$\endgroup\$
  • \$\begingroup\$ What do you want to improve? Speed, looks? \$\endgroup\$ – Kyslik May 31 '18 at 7:51
  • \$\begingroup\$ @Kyslik Both if possible, if not I want to optimize for performance \$\endgroup\$ – derrickrozay May 31 '18 at 14:22
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ – Kyslik Jun 3 '18 at 7:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.