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.

mu is too short · Accepted Answer · 2017-12-21 19:27:50Z

4

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.

answered Dec 21, 2017 at 19:27

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abdullah · Accepted Answer · 2017-12-23 14:10:24Z

3

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

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

edited Dec 23, 2017 at 14:10

answered Dec 21, 2017 at 18:50

Abdullah

2,1212 gold badges22 silver badges30 bronze badges

1 Comment

KavitaC Over a year ago

You are a life saver! Thanks so much. I spent so much time on this.

Collectives™ on Stack Overflow

Rails SQL: Creating a query dynamically

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related