In Ruby on Rails ActiveRecord::Relation#merge overwrites existing conditions on the same column. This may cause the relation to select more records than expected:
authorized_users = User.where(id: [1, 2])
filtered_users = User.where(id: [2, 3])
authorized_users.merge(filtered_users).to_sql
# => SELECT * FROM users WHERE id IN (2, 3)
The merged relation select the users (2, 3), although we are only allowed to see (1, 2). The merged result should be (2).
This card explores various workarounds to combine two scopes so the result is an intersection of both conditions.
Alternative: Use where chains
Chaining multiple where conditions on the same column still works as expected:
User.where(id: [1, 2]).where(id: [2, 3]).to_sql
# => SELECT* FROM users WHERE id IN (1, 2) AND id IN (2, 3)
Alternative: Subselects
You cannot chain where if a scope was created by other code. There is just no way to intersect two given scopes using where:
authorized_users = Power.current.users
filtered_users = User::Search.match(query)
authorized_users.where(???)
For such cases you can use a subselect like this:
authorized_users = Power.current.users
filtered_users = User::Search.match(query)
authorized_users.where(id: filtered_users)
# => SELECT * FROM users WHERE id IN (1, 2) AND id IN (SELECT id FROM users WHERE id IN (2, 3))
Alternative: Relation#and
You can use Relation#and instead:
authorized_users = User.where(id: [1, 2])
filtered_users = User.where(id: [2, 3])
authorized_users.and(filtered_users).to_sql
# => SELECT * FROM users WHERE id IN (1, 2) AND id IN (2, 3)
Unfortunately #and has a big limitation in that both relations must only differ in their conditions (#where, #having). If either side has a #join, #includes, etc., it will throw an error:
authorized_users.and(filtered_users.join(:account)).to_sql
# => ArgumentError: Relation passed to #and must be structurally compatible. Incompatible values: [:joins]
This is being worked on:
- Issue:
Support for combining structurally incompatible relations with
andShow archive.org snapshot - Pull request: Ignore joins on #and calls Show archive.org snapshot
Alternative: Use SQL snippets
When you call #where() with an SQL snippet string instead of a hash, ActiveRecord will no longer overwrite existing conditions:
authorized_users = User.where('id IN ?', [1, 2])
filtered_users = User.where('id IN ?', [2, 3])
authorized_users.merge(filtered_users).to_sql
# => SELECT * FROM users WHERE id IN (1, 2) AND id IN (2, 3)
