CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
frank
frank

Reputation: 1303

Active Record query optimizations for two many-to-many join models

I am displaying the following table for a Product model in Rails 4 with ActiveRecord and Postgres SQL:

enter image description here

Here is my ERD, where I am using a couple of many-to-many models, each arrow here represents a one-to-many relationship:

enter image description here

Question: Here is what I have in my modesl and products/show.html.erb, displaying the table. It is making 35 queries to get each ImpactEntry, and taking a while to load. I would like to optimize that down and looking for suggestions.

Models:

class Product < ActiveRecord::Base
  ...
  has_many :product_impact_line_items, dependent: :destroy
  has_many :impact_line_items, through: :product_impact_line_items
  ...
end

class ProductImpactLineItem < ActiveRecord::Base
  belongs_to :product
  belongs_to :impact_line_item

  has_many :impact_entries, dependent: :destroy
  has_many :categories, through: :impact_entries

  def find_impact_entry(category)
    impact_entries.find_by_category_id(category.id)
  end

end

class ImpactEntry < ActiveRecord::Base
  belongs_to :product_impact_line_item
  belongs_to :category

end

class ImpactLineItem < ActiveRecord::Base
  has_many :product_impact_line_items, dependent: :destroy
  has_many :products, through: :product_impact_line_items

  validates :name, uniqueness: true

end

class Category < ActiveRecord::Base
  has_many :impact_entries, dependent: :destroy
  has_many :categories, through: :impact_entries
  validates :name, uniqueness: true
end

View:

<table id="impacts-table" class="table table-bordered table-medium">
  <thead>
    <tr>
      <th class="col-md-2"></th>
      <% Category.all.each do |category| %>
          <th class="col-md-2"><%= category.name %></th>        
      <% end %>
    </tr>

  </thead>

  <tbody>
    <% product_impact_line_items = @product.product_impact_line_items %>
    <% product_impact_line_items.all.each do |product_impact_line_item| %>
        <tr>
          <th scope="row"><%= product_impact_line_item.impact_line_item.name %></th>
           <% Category.all.each do |category| %>
               <%# byebug %>
               <td class="col-md-2"> <%= product_impact_line_item.find_impact_entry(category).value %></td>
           <% end %>
        </tr>

    <% end %>

 </tbody>
</table>

Upvotes: 0

Views: 44

Answers (1)

Bart
Bart

Reputation: 2656

  1. Use ProductImpactLineItem.includes(:impact_line_item, :impact_entries) instead of ProductImpactLineItem.all (http://apidock.com/rails/ActiveRecord/QueryMethods/includes)
  2. Change impact_entries.find_by_category_id(category.id) to impact_entries.find{ |ie| ie.category_id == category.id } so it won't call the DB each time
  3. Use the bullet gem to detect n+1 queries in the future

Upvotes: 1

Related Questions