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.
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)
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))
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:
and
Show archive.org snapshot
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)