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.

This question is just for curiosity - I'm just looking for a better way that would be easier to compose with varying input like arrays of hashes for example. — max, Jan 03 '20 at 23:41

score 3 · Accepted Answer · answered Jan 07 '20 at 16:50

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'))"

Does exactly as advertised on PG as well. Well done! – max Jan 07 '20 at 17:02 — max, Jan 07 '20 at 17:02

score -1 · Answer 2 · answered Jan 07 '20 at 09:06

-1

This will still generate long query with 'OR' in between. But I felt this is lil elegant/different approach to achieve what you want.

ut = User.arel_table
columns = [:country, :occupation]
pairs = [['dk', 'nurse'], ['ch', 'doctor']]

where_condition = pairs.map do |pair|
  "(#{ut[columns[0]].eq(pair[0]).and(ut[columns[1]].eq(pair[1])).to_sql})"
end.join(' OR ')

User.where(where_condition)

answered Jan 07 '20 at 09:06

Prabakaran

355
2
10

It's not what I want at all and the same thing can be done with `.or(where(...))` – max Jan 07 '20 at 10:12
I don't see anything particularly advantageous to avoid 'or' within 'where' in this case. You have mentioned that you want to experiment with different inputs. May be we can figure something out if you can share available inputs. – Prabakaran Jan 07 '20 at 11:29
`WHERE (columns) IN (values)` can use an index only scan where the OR clause will force a BITMAP OR scan. The former is just much easier for the database to optimize. – max Jan 07 '20 at 11:34

score -1 · Answer 3 · answered Jan 07 '20 at 11:31

-1

I have tried this different approach at my end. Hope it will work for you.

class User < ActiveRecord::Base
  COLUMNS = %i(
    country
    occupation
  )

  PAIRS = [['dk', 'nurse'], ['ch', 'doctor']]

  scope :with_country_occupation, -> (pairs = PAIRS, columns = COLUMNS) { where(filter_country_occupation(pairs, columns)) }

  def self.filter_country_occupation(pairs, columns)
    pairs.each_with_index.reduce(nil) do |query, (pair, index)|
      column_check = arel_table[columns[0]].eq(pair[0]).and(arel_table[columns[1]].eq(pair[1]))
      if query.nil?
        column_check
      else        
        query.or(column_check)
      end
    end.to_sql
  end 
end

Call this scope User.with_country_occupation let me know if it works for you.

Thanks!

answered Jan 07 '20 at 11:31

Akshay Borade

2,442
12
26

Read the question again. I'm not asking about for more bad suggestions on how to do a WHERE AND OR. – max Jan 07 '20 at 11:34
Please check this blog for `Dynamic SQL Generation`. I have just tried to make it dynamically https://www.viget.com/articles/composable-sql-queries-in-rails-using-arel/ – Akshay Borade Jan 07 '20 at 11:36
I have read that article and it contains nothing relevant to my question. Have you? – max Jan 07 '20 at 11:38
As per your question heading `Is there a way to programatically create a where clause in Arel where the columns and values are specified separately?` I have tried this. At that time you didn't mentioned `how to do a WHERE AND OR.` – Akshay Borade Jan 07 '20 at 11:40

score -2 · Answer 4 · answered Jan 07 '20 at 07:19

-2

I think we can do this with Array Conditions as mentioned here

# notice the lack of an array as the last argument
Model.where("attribute = ? OR attribute2 = ?", value, value)

Also, as mentioned here we can use an SQL in statement:

Model.where('id IN (?)', [array of values])

Or simply, as kdeisz pointed out (Using Arel to create the SQL query):

Model.where(id: [array of values])

I have not tried myself, but you can try exploring with these examples.

Always happy to help!

answered Jan 07 '20 at 07:19

Sreeram Nair

2,369
12
27

I fear the question asked has a different expectation. There should be multiple pairs to match with 'OR' in between. – Prabakaran Jan 07 '20 at 08:23
@Prabakaran is correct here. `WHERE id IN 1,2,3` will match any record with the id 1,2,3. `WHERE id, foo IN ((1,2),(3,4))` is closer to a AND OR. – max Jan 07 '20 at 10:26

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

4 Answers4