Create a WHERE (columns) IN (values) clause with Arel?

Question

Is there a way to programatically create a where clause in Arel where the columns and values are specified separately?

SELECT users.*
WHERE (country, occupation) IN (('dk', 'nurse'), ('ch', 'doctor'), ...

Say the input is a really long list of pairs that we want to match.

I'm am NOT asking how to generate a WHERE AND OR clause which is really simple to do with ActiveRecord.

So far I just have basic string manipulation:

columns = [:country, :occupation]
pairs = [['dk', 'nurse'], ['ch', 'doctor']]
User.where(
  "(#{columns.join(', ')}) IN (#{ pairs.map { '(?, ?)' }.join(', ')})", 
  *pairs
)

Its not just about the length of the query WHERE (columns) IN (values) will also perform much better on Postgres (and others as well) as it can use an index only scan where OR will cause a bitmap scan.

I'm only looking for answers that can demonstrate generating a WHERE (columns) IN (values) query with Arel. Not anything else.

All the articles I have read about Arel start building of a single column:

arel_table[:foo].eq...

And I have not been able to find any documentation or articles that cover this case.

Answer 1

The trick to this is to build the groupings correctly and then pass them through to the Arel In Node, for example:

columns = [:country, :occupation]
pairs = [['dk', 'nurse'], ['ch', 'doctor']]

User.where(
    Arel::Nodes::In.new(
        Arel::Nodes::Grouping.new( columns.map { |column| User.arel_table[column] } ),
        pairs.map { |pair| Arel::Nodes::Grouping.new(
            pair.map { |value| Arel::Nodes.build_quoted(value) } 
        )}
    )
)

The above will generate the following SQL statement (for MySQL):

"SELECT users.* FROM users WHERE (users.country, users.occupation) IN (('dk', 'nurse'), ('ch', 'doctor'))"