CODEWITHSUNDEEP
sqlruby-on-railsrubyruby-on-rails-5
LutherSmi
LutherSmi

Reputation: 73

Rails Dynamic 'Where' clause

I have a search field where the user can enter "Param1" and my controller contains:

Model.where('column_name LIKE ?', "%#{search_field}%")

This is converted to:

SELECT ... FROM table WHERE column_name LIKE '%Param1%'

This works fine if the user enters "Param1" but I want make it comma separated search field.

So if the user enters "Param1,param2" or "Param1,Param2,Param3" the WHERE LIKE clause should work, My idea is to split the string based on ',' and have absolutely no idea on how to build the Model.where clause here, since it can be 1 parameter or 4.

Upvotes: 3

Views: 3406

Answers (4)

user1201917
user1201917

Reputation: 1370

Splitting the parameters is a good idea.

conditions = "Param1,Param2,Param3, Param4".gsub(" ","").split(',')

If search without masks or regular expressions is enough, you can use IN query or AR: Model.where(column_name: conditions)

Otherwise (at least in postgresql) the code becomes slightly more complicated.

Construct the query string from the input string:

conditions = "Param1,Param2,Param3, Param4".gsub(" ","").split(',')

query = ("column_name ~* ? OR " * conditions.size)[0...-3] # Delete last unterminated " OR"

And finally

Model.where(query, *conditions) # splat operator (*) converts array into argument list

Should work.

Upvotes: 1

arieljuod
arieljuod

Reputation: 15838

This is what I do on one project:

values = search_field.split(',').map { |x| "%#{x}%" } # split values and add "%...%"
count = values.count # count them

# create an array of LIKE operators depending on the values counted
# if count is 2 then it will be an array: ['column_name LIKE ?', 'column_name LIKE ?']
where_sql_arr = count.times.map{'column_name LIKE ?'} 

# create the sql query joining with " OR "
# 'column_name LIKE ? OR column_name LIKE ?'
where_sql = where_sql_arr.join(' OR ') 

Model.where(where_sql, *values) # note the * before "values" to split the array into multiple arguments

Upvotes: 3

Sebastián Palma
Sebastián Palma

Reputation: 33420

In case you're using PostgreSQL, you can use ANY passing the mapped array of params:

Model.where('name LIKE ANY(array[?])', params.split(',').map { |val| "%#{val}%" })
# SELECT "models".*
# FROM "models"
# WHERE (name LIKE ANY(array['%param1%','%param2%']))

Upvotes: 4

3limin4t0r
3limin4t0r

Reputation: 21110

The answer uses the same method as the answer of arieljuod, but uses the Arel API instead of using plain strings.

values = search_field.split(',')
column_name = Model.arel_table[:column_name]
query = values.map { |value| column_name.matches("%#{value}%") }.reduce(:or)
Model.where(query)

Upvotes: 3

Related Questions