Laravel Eloquent / SQL - search for keywords in db

Question

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?

score 4 · Answer 1 · answered Apr 15 '18 at 15:35

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.

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

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

edited May 23 '17 at 11:45

Community

1
1

answered Feb 05 '17 at 14:19

marcothesane

6,192
1
11
21

Thx for the answer but this sounds very difficult.. Is there any easier way to do this? – nameless Feb 05 '17 at 14:32
In that case, 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. – marcothesane Feb 05 '17 at 14:38
but this is basically what `LIKE` would do, too, isn't it? Because how could I use your idea when using `hose,blaue hose`? Should end in the product with the keywords `hose,jeans,blaue hose,kleidung` – nameless Feb 05 '17 at 14:41
If what you mean is that you give a list of keywords as input, and hope to find a match if you search for 'hose, blaue hose' in 'hose,jacke,jeans,blaue hose' - then this is not a SQL question you could ask. You would have to programmatically convert the search condition to `WHERE keywords LIKE '%hose% OR keywords LIKE '%blaue hose%'`. – marcothesane Feb 05 '17 at 14:45
okay, you might be right, but if I use an OR like you suggested now, I would run in the issue, that there would also be results that only contain `hose` or `blaue hose`, but not both together. But I think you got my idea. I have a product with a list of keywords, then I want to be able to pass a list of keywords to a search field and I want to find products that contain all keywords, passed from the search box, no matter in which order they are saved for the product – nameless Feb 05 '17 at 14:49
And in that case, I would go through the vertical splitting as explained in my answer, at least if it is a big table, to have the chance of dramatically speeding up the search. There, you could actually put an `IN('hose','blaue hose')` into the WHERE condition and use an index – marcothesane Feb 05 '17 at 14:49
Then, AND istead of OR – marcothesane Feb 05 '17 at 14:50
I'll try that idea, propably won't be too easy, because I don't know how many keywords are passed so I need to split the `keywords` string on the comma and need to add a `AND ...` for every keyword in the array (that contains the spliited string) – nameless Feb 05 '17 at 14:52

Laravel Eloquent / SQL - search for keywords in db

2 Answers2