Laravel Eloquent / SQL - search for keywords in db

Sorry, I know I'm late for the party but this is the first result in Google when I was looking for Eloquent keywords search. I had the same problem and I want to help with my solution.

$q = $request->input('productSearch');
$needles = explode(',', $q);

// In my case, I wanted to split the string when a comma or a whitespace is found:
// $needles = preg_split('/[\s,]+/', $q);

$products = Products::where('name', 'LIKE', "%{$q}%");

foreach ($needles as $needle) {
    $products = $products->orWhere('name', 'LIKE', "%{$needle}%");
}

$products = $products->paginate(15);

If the user input has too many commas, the $needles array could be too large (and the query too huge), so you can limit the search, for example, for only the first 5 elements in the array:

$needles = array_slice($needles, 0, 5);

I hope this can help somebody.

On your reply just now:

If you want it simpler, read this MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/regexp.html.

Basically, in a file you could grep for [,]?blaue hose[,]? to find: an optional comma, the string 'blaue hose', and an optional comma.

The more solid solution would be my initial answer:

You could actually create a keyword table, depending on your products table, where each keyphrase/keyword is in one column by itself, and even lay an index on the keyphrase/keyword. I explain the principle here:

Optimising LIKE expressions that start with wildcards

And, to take your example as input - here is how I do that in Vertica. Many databases offer a function that returns the n-th part/token of a string delimited by a character of your choice. In Vertica, it's SPLIT_PART().

MySQL, unfortunately, does not offer any correspondence to that function, and you would have to convert the Common Table Expressions in the WITH clauses below to in-line SELECT-s (SELECT ... FROM (SELECT ... ) AS foo(col1,col2,col3) ..... And then, there is a suggestion here From Daniel Vassallo on how to tackle it:

Split value from one field to two

In Vertica, it would look like this:

WITH 
-- input
products(prod_id,keywords) AS (
          SELECT 1001,'auto,cabrio,frischluft'
UNION ALL SELECT 1002,'hose,jeans,blaue hose,kleidung'
)
,
-- index to get the n-th part of the comma delimited string
max_keyword_count(idx) AS (
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
)
SELECT
  prod_id
, idx
, TRIM(SPLIT_PART(keywords,',',idx)) AS keywords
FROM products
CROSS JOIN max_keyword_count
WHERE SPLIT_PART(keywords,',',idx) <> ''
ORDER BY
  prod_id
, idx
;

prod_id|idx|keywords
  1,001|  1|auto
  1,001|  2|cabrio
  1,001|  3|frischluft
  1,002|  1|hose
  1,002|  2|jeans
  1,002|  3|blaue hose
  1,002|  4|kleidung