Eagerloading with scoping in rails3

Question

I have been trying to eager load associations based on some scope in my rails3 app, but could not find any solution.

My app has following models:

class Project
 has_many :entries
 has_many :to_dos

class ToDo
 has_may :entries
 has_many :tasks
 belongs_to :project

class Task
 has_many :entries
 belongs_to :to_do

class Entry
belongs_to :project
belongs_to :to_do
belongs_to :task

# options format: {:from_date=>(Date.today-1.week), :to_date=>(Date.today+1.week), :user_id=>60}
scope :filtered_list, lambda { |options|
  condition = options[:user_id].nil? ? "true" : "user_id = #{options[:user_id]}"
  condition += options[:from_date].nil? ? "" : " AND entry_date >= '#{options[:from_date]}'"
  condition += options[:to_date].nil? ? "" : " AND entry_date <= '#{options[:to_date]}'"
  where(condition)
}

And in projects#index i have following code to get all projects of an user:

@projects = current_user.projects.includes(:entries, :to_dos =>[:entries, :tasks => :entries])

It fetches all projects of the user, along with eager loading the associations. So when i perform following loop to get all the entries within the project, no new query gets fired.

def all_entries(options)
  entries = self.entries
  self.to_dos.each do |d|
    entries += d.entries
    d.tasks.each do |t|
      entries += t.entries
    end
  end
end

As this eager loading fetches all entries, it is way too much data than what I actually needed. So I tried to apply some conditions to the entries eager loaded, but could not find any solution. I was looking for something like:

@projects = current_user.projects.includes(:entries.filtered_list(options), :to_dos =>[:entries.filtered_list(options), :tasks => :entries.filtered_list(options)])

So that only the entries satisfying some conditions get loaded.

Can't we use scoping with eager loading? Please help me out use eagerloading alongside scoping.

Answer 1

As far as I know, scopes cannot be applied to included associations like this. However, you can specify conditions that should only be applied to the eager loading queries. So with a bit of refactoring, you could have a method that only created the conditions you currently define in your scope:

def self.filter_by(options)
  condition = options[:user_id].nil? ? "true" : "entries.user_id = #{options[:user_id]}"
  condition += options[:from_date].nil? ? "" : " AND entries.entry_date >= '#{options[:from_date]}'"
  condition += options[:to_date].nil? ? "" : " AND entries.entry_date <= '#{options[:to_date]}'
  condition
end

or a bit more rubyesque:

def self.filter_by(options)
  conditions = []
  conditions << "entries.user_id = #{options[:user_id]}" unless options[:user_id].nil?
  conditions << "entries.entry_date >= '#{options[:from_date]}'" unless options[:from_date].nil?
  conditions << "entries.entry_date <= '#{options[:to_date]}'" unless options[:to_date].nil?
  conditions.join(" AND ")
end

and then chain that method to your eager loading:

@projects = current_user.projects.includes(:entries, :to_dos =>[:entries, :tasks => :entries].where(Entry.filter_by(options))

and also reuse it in your scope if you need it independently:

scope :filtered_list, lambda { |options| where(Entry.filter_by(options)) }

Disclaimer: None of this is tested with your actual model definitions, but it works fine with some pretty equivalent ones that I had lying around.

Also note that if the filter options ultimately come from the client side, your condition is vulnerable to SQL injection.

Behind the scenes, Rails uses a JOIN to load the relevant data, so that is something to be aware of. It might be a good thing (a few less queries) or a bad thing (if your indexing is suboptimal). That's probably why the guide has this to say:

Even though Active Record lets you specify conditions on the eager loaded associations just like joins, the recommended way is to use joins instead.