Read more

Preloaded associations are filtered by conditions on the same table

Arne Hartherz
December 09, 2010Software engineer at makandra GmbH

When you eagerly load an association list using the .include option, and at the same time have a .where on an included table, two things happen:

  1. Rails tries to load all involved records in a huge single query spanning multiple database tables.
  2. The preloaded association list is filtered by the where condition, even though you only wanted to use the where condition to filter the containing model.
Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

The second case's behavior is mostly unexpected, because pre-loaded associations usually don't care about the circumstances under which their containing model was found.

Example

Take this class:

class Activity
  has_many :users
end

Here activity 42 has four users:

activity = Activity.find(42)
activity.users.ids
# => [1, 2, 3, 4]

Let's say we want to do the same on all activities that belong to a user. To this, we scope on Activity.
Note how the activity from above now suddenly no longer contains any other users:

activity = Activity.
  .includes(:users)
  .where(users: { id: [4] })
  .find(42)
  
activity.users.ids # here happens the unexpected
# => [4]

By reloading the object, its full list of associated users is restored:

activity.reload.users.ids
# => [1, 2, 3, 4]

Or you can reset the association cache:

activity.users.reset # newer Rails
activity.users(true) # old Rails

In newer Rails versions you should prefer to use joins and then preload if necessary (which will trigger a second query):

# Join for the condition, but do not preload
activities = Activity.joins(:users).where(users: { id: [4] })

# Preload associations with a second query on users;
# Does not make a join.
activities = activities.preload(:users)
Posted by Arne Hartherz to makandra dev (2010-12-09 15:08)