ActiveRecord Nested Arrays

Question

Do any methods / patterns exist for sanitizing an array of arrays when using it as SQL input?

Looking to implement the following style of query in ActiveRecord:

SELECT *
FROM "addresses"
WHERE ("addresses"."city", "addresses"."state", "addresses"."country") IN (
  ('Juneau', 'AK', 'US'), 
  ('Albany', 'NY', 'US'),
  ...
)

For example:

searches = [
  ['Juneau', 'AK', 'US'],
  ['Albany', 'NY', 'US'],
]

searches_sql = searches.map do |search| 
  "(#{search.map { |query| Address.connection.quote(query) }.join(', ')})"
end.join(', ')
Address.where(%(("addresses"."city", "addresses"."state", "addresses"."country") IN (#{searches_sql})))

Works but relies on some manual connection escaping that doesn't seem ideal (and isn't easily generalized beyond this scope).

max · Accepted Answer · 2020-05-29 11:03:13Z

2

ActiveRecord does not actually have a built in way to construct a WHERE columns IN values query.

But you can do it with Arel:

class Address
  def self.search(sets)
    where(
      Arel::Nodes::In.new(
        [:city, :state, :country].map { |c| arel_table[c] },
        sets.map do |set|
          Arel::Nodes::Grouping.new(
            set.map {|value| Arel::Nodes.build_quoted(value)}
          )
        end
      )
    )
  end
end

answered May 29, 2020 at 11:03

max

103k15 gold badges113 silver badges177 bronze badges

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

2 Comments

Kevin Sylvestre Over a year ago

This is interesting - thanks!, I'm not sure about the statement that ActiveRecord does not have a way to build WHERE IN queries. For example Address.where(city: %w[Juneau Albany]) generates a IN query SELECT "addresses".* FROM "addresses" WHERE "addresses"."city" IN ('Juneau', 'Albany'). Also appears to have some formatting issues around brackets (i.e. doesn't treat the city / state / country as a tuple).

max Over a year ago

Yea but AR will only create a query for a single column, not to match a list of columns to sets of values like the query you are trying to create.

Collectives™ on Stack Overflow

ActiveRecord Nested Arrays

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related