references - rails prefetch




Preload has_many associations with dynamic conditions (4)

As I understand, you want to fetch all the places that has at least one event satisfying some condition, but places should be fetched with all events list even those which doesn't satisfy condition. You can't figure this is out with one simple query, but if you will use suquery then issue will done. Here is the solution:

Place.includes(:events).where(id: Place.joins(:events).where("events.start_date > '#{time_in_the_future}'")).references(:events)

There is one complex query will be constructed, but it do the things right.

I have a Place model and an Event model. Places can have events that take place on a specific date.

How can I set up my associations and finders to load all places including (eager loading) their events at a specific date without N+1 query problem?

What I've tried:

class Place
    has_many :events
end

Place.all.preload(:events).where("events.start_date > '#{time_in_the_future}'")
#ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "events".

Place.all.includes(:events).where("events.start_date > '#{time_in_the_future}'").references(:event)
# only loads places that have an event at the specific date and not all places including their events (if there are any events).

I successfully came up with an association that does what I want but is not dynamic (does not accept parameters)

class Place
    has_many :events, -> {where("events.start_date > '#{Time.now}'")}
end

Place.all.preload(:events)
# perfect: executes two queries: One to get all 'places' and one to get all 'events' that belong to the places and merges the 'events' into the 'place' objects. 
# But I can't pass time as a parameter, so time is always Time.now (as specified in the has_many association). 
# Place.all.preload(:events).where(xyz) gives wrong results like the examples above.

The problem for me is that I can't find a way to preload/eager load with dynamic conditions. Because preload and includes expect the association name as a parameter and can´t be refined with parameters. At least I found no way to do this.


This seems to be the only solution that works:

# 1st query: load places
places = Place.all.to_a

# 2nd query: load events for given places, matching the date condition
events = Event.where(place: places.map(&:id)).where("start_date > '#{time_in_the_future}'")
events_by_place_id = events.group_by(&:place_id)

# 3: manually set the association
places.each do |place|
  events = events_by_place_id[place.id] || []

  association = place.association(:events)
  association.loaded!
  association.target.concat(events)
  events.each { |event| association.set_inverse_instance(event) }
end

It's a bit hacky but it's quite easy to adapt to any situation where you might want to load an association using a separate query and then attach it to an existing object.

All credit goes to https://mrbrdo.wordpress.com/2013/09/25/manually-preloading-associations-in-rails-using-custom-scopessql/


Include associated model for all objects (in index action)

You want to find the comment's rating where the rating's user_id matches the current user.

<%= comment.ratings.where(user_id: current_user.id).first %>

However this sort of logic is pretty cumbersome in the views, a better strategy would be to define a scope in Rating that returns all ratings made by a specific user.

class Rating
  scope :by_user, lambda { |user| where(user_id: user.id) }
end

class Comment
  # this will return either the rating created by the given user, or nil
  def rating_by_user(user)
    ratings.by_user(user).first 
  end
end

Now in your view, you have access to the rating for the comment created by the current user:

<% @comments.each do |comment| %>
  <%= comment.rating_by_user(current_user) %>
<% end %>

If you want to eager load all ratings in your index page, you can do the following:

def index
  @comments = page.comments.includes(:ratings)
end

You can then find the correct rating with the following:

<% @comments.each do |comment| %>
  <%= comment.ratings.find { |r| r.user_id == current_user.id } %>
<% end %>

This would return the correct rating without generating any extra SQL queries, at the expense of loading every associated rating for each comment.


I'm not aware of a way in ActiveRecord to eager load a subset of a has_many relationship. See this related question, as well as this blog post that contains more information about eager loading.


Rails filter associated records

Unfortunately, there is no elegant solution for this problem. It's possible to preload association with static filter, e.g.:

User.eager_load(:popular_comments)

but it doesn't work with dynamic filer.

You can find excellent article about preloading Rails associations here http://blog.arkency.com/2013/12/rails4-preloading/