ActiveRecord::Relation#merge overwrites existing conditions on the same column

Updated . Posted . Visible to the public. Repeats.

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:

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)
Henning Koch
Last edit
Henning Koch
Keywords
override, overrides, attribute
License
Source code in this card is licensed under the MIT License.
Posted by Henning Koch to makandra dev (2023-05-22 08:38)