CODEWITHSUNDEEP
phpmysqlsqllaraveleloquent
nameless
nameless

Reputation: 1521

Laravel Eloquent / SQL - search for keywords in db

I'm currently trying to implement a search for keywords/tags in my db.

In my db, I have lines with keywords like: auto,cabrio,frischluft or hose,jeans,blaue hose,kleidung

so always some keywords (that can basically also have a whitespace), seperated by a comma (,).

Now I want to be able to find a product in my db that has some keywords entered.

With LIKE I can find the two entries I mentioned with queries like auto,cabrio or also cabrio,frischluft or hose,jeans,blau or hose,kleidung. But what happens if I enter auto,frischluft or something like hose,blaue hose or jeans,kleidung?

Then LIKE wont work any more. Is there a way to do this?

I hope you know what I mean...

So just to make it clear: The code I currently use is: $searchQuery = "%".$request->input('productSearch')."%"; and $products = Product::where('name', 'LIKE', $searchQuery)->paginate(15);

But as I said, this won't bring me back the article with the keyowrds auto,cabrio,frischluft if the input productSearch has the keywords auto,frischluft entered...

Any ideas?

Upvotes: 0

Views: 2616

Answers (2)

shino47
shino47

Reputation: 213

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.

Upvotes: 4

marcothesane
marcothesane

Reputation: 6721

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

Upvotes: 0

Related Questions