Rails SQL: Creating a query dynamically

Question

I am trying to create a dynamic query based on User inputs

if a
  conditions << ["fname like (?)"]
  sql_params <<  "abc"
end
if b
  conditions << ["lname like (?)"]  
  sql_params <<  "def"  
end
if c
  cconditions << ["middle like (?)"]  
  sql_params <<  "xyz"   
end

results = Model.where(conditions.join(' AND '), sql_params)

While the conditions get in correct syntax, the sql_params are listed as an array. This forms the below query

Model.where("fname like (?) AND lname like (?) AND middle like (?)", ["abc","def","xyz"])

while what I need is

Model.where("fname like (?) AND lname like (?) AND middle like (?)", "abc","def","xyz")

I tried several map/join etc options on the sql_params array but nothing worked.

Answer 1

Are you sure you need to produce exactly this?

Model.where("fname like (?) AND lname like (?) AND middle like (?)", "abc","def","xyz")

Something of the form:

Model.where('expr1 AND expr2 AND expr3')

is equivalent to:

Model.where('expr1').where('expr2').where('expr3')

so why not build the query piece by piece rather than messing around with strings? Something like this:

query = Model.all
query = query.where('fname  like ?', 'abc') if a
query = query.where('lname  like ?', 'def') if b
query = query.where('middle like ?', 'xyz') if c

will give you the same result.

Answer 2

You need to pass sql_params with * (known as splat operator) i.e.

results = Model.where(conditions.join(' AND '), *sql_params)