CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Mikey Hogarth
Mikey Hogarth

Reputation: 4702

How to achieve this join in Active Record?

I'm working on an application that has the following associations:

class Chart < ActiveRecord::Base
  belongs_to :chart_group
  has_many :charts, :through => :chart_groups
end

class ChartGroup < ActiveRecord::Base
  has_many :charts
  belongs_to :report
end

class Chart < ActiveRecord::Base
  belongs_to :chart_group
end

One part of the application requires that we display the top 20 reports, and for each report we display the first chart (as kind of a snapshot). They way i've been doing this is, in the controller just selecting the reports;

@reports = Report.limit(20)

and then in the views just doing;

@reports.each do |report|
   <%= report.charts.first.title %>
end

Which seems fine and runs fast, but falls foul of the "N + 1 queries problem" - i would be getting one database round trip per report, so 21 hits. So I have a couple of questions

1) How can I change my original query to get the first chart? I tried using ".joins" but the problem with that is that there can be LOADS of charts associated with any one report, and there can be hundreds of reports. I can't work out how to include the "only the first chart" part into the statement.

2) Actually doing it the way I am, I have noticed that the logs are all saying "CACHE LOAD" - is rails doing some magic here for me? Am I worrying about nothing?

Upvotes: 1

Views: 71

Answers (1)

Nazar
Nazar

Reputation: 1509

Rail's Eager Loading should help here.

The following should load all charts associated with requested reports:

@reports = Report.limit(20).includes(:charts)

AR will load all requested report limit(20) and find all charts associated with these reports and also load these into memory.

To load only the first chart into memory for each report requires additional work:

  • add an additional column to reports.first_chart_id
  • define an additional association from Report to Chart in relation to first_chart_id

Report should be modified as follows:

class Report < ActiveRecord::Base
  belongs_to :first_chart, :foreign_key => 'first_chart_id', :class_name => 'Chart'
end

Then:

@reports = Report.limit(20).includes(:first_chart)

The only caveat with the above is that first_chart_id will have to be set during Report instance create/update.

Another solution is to perform two DB queries:

Fetch all reports then iterate through fetched reports, extract @report.id then query charts to return the first chart only (no idea how the first chart is defined)

@reports = Report.limit(20)
@report_ids = @reports.collect{|r| r.id}
@first_charts = Chart.find_by_report_ids(@report_ids)


class Chart
  def self.find_by_report_ids(report_ids)
    {}.tap do |result|
      #not sure what your scheme is like for Report and Chart... so guessing  
      where(:id => report_ids).group(:report_id).each do |chart|
        result[chart.report_id] = chart
      end
    end
  end
end

find_by_report_ids returns a hash of charts indexed by report.id, which gives you quick access to a report's first chart in your view

@reports.each do |report|
   <%= @first_charts[report.id].title %>
end

Unfortunately sometimes you have to write additional code for many-to-many type relationships but these can be mostly solved by using two DB trips, which is still much better than O(N) queries.

HTH

Upvotes: 2

Related Questions