CODEWITHSUNDEEP
ruby-on-railsactiverecord
konyak
konyak

Reputation: 11706

ActiveRecord find records that start with one of the words in an array

I know this below query will find records that have a field's value starting with a certain prefix.

MyModel.where("field LIKE ?", "#{prefix}%")

What is the query to find records that have a field's value starting with one of the words in an array?

EDIT: I found solutions to other languages here: SQL LIKE % inside array and btw, I'm using MySQL DB.

Upvotes: 2

Views: 2621

Answers (1)

MrYoshiji
MrYoshiji

Reputation: 54882

This should do it:

prefixes = ['mega', 'hyper', 'super']
sql_conditions = prefixes.map{ |prefix| "field ILIKE #{sanitize("#{prefix}%")}" }.join(' OR ')
MyModel.where(sql_conditions)

In my IRB console:

prefixes = ['mega', 'hyper', 'super']
sql_conditions = prefixes.map{ |prefix| "field ILIKE #{ActiveRecord::Base.sanitize("#{prefix}%")}" }.join(' OR ')
# => "field ILIKE 'mega%' OR field ILIKE 'hyper%' OR field ILIKE 'super%'"

An alternative, for MySQL only:

prefixes = ['^mega', '^hyper', '^super'] # the ^ wildcard represents the start of line
sql_conditions = "field RLIKE '#{prefixes.join('|')}'" # RLIKE is also known as REGEXP
MyModel.where(sql_conditions)

Upvotes: 3

Related Questions