Can I find item on database with given array of string but using partial string?

Question

I have model Product with name and category

On seed:

Product.create(name: "apple", category: "food")
Product.create(name: "biogesic", category: "medicine")

And a 2 dimensional array:

[[1, "tray of apple", 150.00], [1, "box of ballpen", 70.30]]

What I need is to get if the string inside the array contains or is on the table/database Product

Here's what I'm thinking but I'm lost:

isProduct = Product.where("name like ?", "%an apple%").first

Where "%apple%" is supposed to be the string on array, but with that code it is limited for 1 word only.

I don't need the product id, I just need it if it is on the Product table.

Answer 1

In a certain way, this can be accomplished with the Regular Expression Operator ~ for PostgreSQL and/or REGEXP for MySQL:

regex = array.flat_map { |_, sentence, _| sentence.split }.join('|')
Product.exists?(['name ~ ?', regex])
Product.exists?(['name REGEXP ?', regex])

Which produces:

SELECT 1 AS one FROM "products" WHERE (name ~ 'tray|of|apple|box|of|ballpen') LIMIT $1  [["LIMIT", 1]]

As it searches for the presence of every single word within the sentences tray of apple and/or box of ballpen.

So, in case you have a record like:

Product.new(name: 'tray of apple and box of ballpen')

It'll cover the query and return true.