预加载 has_many 与动态条件的关联 [英] Preload has_many associations with dynamic conditions

问题描述

我有一个 Place 模型和一个 Event 模型.地点可以有在特定日期发生的事件.

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

如何设置我的关联和查找器以在特定日期加载所有地点，包括(急切加载)他们的事件而不会出现 N+1 查询问题?

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?

我尝试过的:

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.

所有功劳归于 https://mrbrdo.wordpress.com/2013/09/25/manually-preloading-associations-in-rails-using-custom-scopessql/

这篇关于预加载 has_many 与动态条件的关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！