CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
enjaku
enjaku

Reputation: 346

passing a column name as a variable to an ActiveRecord query

Let's say I have a query:

Foo.where("lower(bar) LIKE ?", "%#{baz}%")

which transforms to plain SQL as:

    ... WHERE (lower(bar) LIKE ...

and works perfectly. But instead of bar I want to pass an argument. So I tried to rewrite it like this:

Foo.where("lower(?) LIKE ?", bar, "%#{baz}%")

where bar is a variable containing a string 'bar'. In this case it stops working and transforms to plain SQL as:

... WHERE (lower('bar') LIKE ...

So how can I make this query work?

Upvotes: 3

Views: 2008

Answers (2)

odlp
odlp

Reputation: 5124

You might want to try using sprintf-style % escapes in the template to avoid the the column name you wish to interpolate from being quoted:

Foo.where("lower(%s) LIKE '%s'", bar, "%#{baz}%")

(N.B. The single quotes around the second %s - the second argument does want to be quoted)

Reference:

Lastly, you can use sprintf-style % escapes in the template. This works slightly differently than the previous methods; you are responsible for ensuring that the values in the template are properly quoted. The values are passed to the connector for quoting, but the caller is responsible for ensuring they are enclosed in quotes in the resulting SQL. After quoting, the values are inserted using the same escapes as the Ruby core method Kernel::sprintf.

Upvotes: 5

Vasfed
Vasfed

Reputation: 18464

You can construct your query via Arel:

bar = :field_name # or 'string_name'.to_sym
Foo.where(Foo.arel_table[bar].lower.matches("%#{baz}%"))

Upvotes: 2

Related Questions