How to build a query with arbitrary placeholder conditions in ActiveRecord?

Question

Assume I have an arbitrary number of Group records and I wanna query User record which has_many :groups, the catch is that users are queries by two bound fields from the groups table.

At the SQL level, I should end up with something like this:

  SELECT * FROM users where (categories.id = 1 OR users.status = 0) OR(categories.id = 2 OR users.status = 1) ... -- to infinity

This is an example of what I came up with:

# Doesn't look like a good solution. Just for illustration.
or_query = groups.map do |g|
"(categories.id = #{g.category.id} AND users.status = #{g.user_status.id} )"
end.join('OR')
User.joins(:categories).where(or_query) # Works

What I think I should be doing is something along the lines of this:

# Better?
or_query = groups.map do |g|
"(categories.id = ? AND users.status = ? )".bind(g.category.id, g.user_status.id) #Fake method BTW
end.join('OR')
User.joins(:categories).where(or_query) # Works

How can I achieve this?
There has to be a better way, right?

I'm using Rails 4.2. So the shiny #or operator isn't supported for me.

score 1 · Answer 1 · edited May 23 '17 at 10:30

I would collect the condition parameters separately into an array and pass that array (splatted, i.e. as an arguments list) to the where condition:

or_query_params = []
or_query = groups.map do |g|
  or_query_params += [g.category_id, g.user_status.id]
  "(categories.id = ? AND users.status = ?)"
end.join(' OR ')
User.joins(:categories).where(or_query, *or_query_params)

Alternatively, you might use ActiveRecord sanitization:

or_query = groups.map do |g|
  "(categories.id = #{ActiveRecord::Base.sanitize(g.category_id)} AND users.status = #{ActiveRecord::Base.sanitize(g.user_status.id)})"
end.join(' OR ')
User.joins(:categories).where(or_query)

How to build a query with arbitrary placeholder conditions in ActiveRecord?

1 Answers1