2

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.

Community
  • 1
  • 1
konyak
  • 10,818
  • 4
  • 59
  • 65

1 Answers1

3

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)
MrYoshiji
  • 54,334
  • 13
  • 124
  • 117