Preload has_many associations with dynamic conditions

Question

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.

Answer 1

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/

Answer 2

To solve the dynamic date problem, have you considered:

class Event < ActiveRecord::Base

  belongs_to :place

  scope :on_date, lambda {|the_date| where(start_date: the_date) }
  scope :on_or_after, lambda {|the_date| where('start_date >= ?', the_date) }
end

You could then do this:

@place = Place.find(params[:id]) # let's say...
@place.events.on_date(params[:chosen_date])

You can incorporate the eager loading stuff that others have mentioned too.