Read more

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

Henning Koch
May 22, 2023Software engineer at makandra GmbH

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)
Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

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
May 22, 2023Software engineer at makandra GmbH
Posted by Henning Koch to makandra dev (2023-05-22 10:38)