Optimizing db queries in Rails 4

Question

I have the following code in a view that lists all customers and the total amount of each customer's orders.

The relationship between the entity types Customer, Order and OrderItem is: Customer-1:n-Order-1:n-OrderItem

view index.rb

<% @customers.each do |c| %>
 <tr>
   <td id="name"><%= c.name %>
   <td id="cus_total"><%= c.orders.map(&:order_total_amount).sum %>
 </tr>

customers_controller.rb

@customers = Customer.find(:all)

model orders.rb

def order_total_amount
 orderitems.sum("amount")
end

I now want to print the total amount of all orders by all customers in the first row of the table. Obviously, I don't want to query the DB again for this task, since the total amount is just the sum of all items in column "cus_total". If I'd want to print the total amount at the bottom of the table, I'd just do

<% @customers.each do |c| %>
 <tr>
  <td id="name"><%= c.name %>
  <td id="cus_total"><%= total += c.orders.map(&:order_total_amount).sum %>
 </tr>
<% end %>
 <tr>
  <td>Total</td>
  <td><%= total %></td>
 </tr>

But what do I do in Rails 4 when I want to print the total amount at the top of the table without querying the database again?

hrnnvcnt · Accepted Answer · 2014-01-04 00:12:40Z

2

I see many ways to achieve your desired result, but i left you here my possible aproach:

I would use a callback to save and update the sum of orders of every client in total_orders (you will need to add a column to your customer model just

rails g migration AddTotalOrdersToCustomers total_orders:float

)

app/model/order.rb

after_save :calculate_sum_of_total_orders
after_destroy :calculate_sum_of_total_orders

def calculate_sum_of_total_orders
  customer.update_column(:total_orders, orders.map(&:order_total_amount).sum)
end

It will run just one QUERY over your customers table because your customer's total is already stored and so then to avoid a query to SUM the total for every customer in your view.

In your controller

@customers = Customer.all

In your view

Just sum your total_orders of your @customers

<tr>
  <td>Total</td>
  <!-- This one line read the variable from memory, no new sql -->
  <td><%= @customers.map(&:total_orders).sum %></td>
</tr>
<% total_customers_sum = 0 %>
<% @customers.each do |c| %>
  <tr>
    <td id="name"><%= c.name %></td>
    <td id="cus_total">
      <%= c.total_orders # just read the column value %>
      <%#= total += c.orders.map(&:order_total_amount).sum %>
      <!-- you don't need to query every order to sum anymore --> 
    </td>
  </tr>
<% end %>

I hope that it will helpful, sorry if my answer is not the most optimal for your needs.

edited Jan 4, 2014 at 0:12

answered Jan 3, 2014 at 20:29

hrnnvcnt

9538 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tolsto Over a year ago

I don't see how this helps me to display the total at the top of the table.

Tolsto Over a year ago

Thank you for your approach, but I'd prefer to keep the database normalized.

hrnnvcnt Over a year ago

Hi, i updated my answer, i think it is better when tries to run just one query to optimize the time of response, in my case i had always a lot orders to sum for every customer and this approach help me to solve my slowness' problem.

Utsav Kesharwani · Accepted Answer · 2014-01-03 20:27:48Z

1

In customers_controller.rb

@customer_details = []
@grand_total = 0
customers = Customer.includes(:order).find(:all)
customers.each do |customer|
  customer_total = customer.orders.map(&:order_total_amount).sum
  @customer_details << { :name => customer.name, :cus_total => customer_total }
  @grand_total += customer_total
end

In view index.rb

<% @customer_details.each do |c| %>
  <tr>
     <td id="name"><%= c[:name] %>
     <td id="cus_total"><%= c[:cus_total] %>
  </tr>
<% end %>
<tr>
  <td>Total</td>
  <td><%= @grand_total %></td>
</tr>

answered Jan 3, 2014 at 20:27

Utsav Kesharwani

1,74512 silver badges25 bronze badges

1 Comment

Michael Choi Over a year ago

Note that customers = Customer.includes(:order).find(:all) will kill your memory if you have a lot of orders per customer. Try this instead

customers = Customer.includes(:order).find(:all).find_each  do |customer|   customer_total = customer.orders.map(&:order_total_amount).sum   @customer_details << { :name => customer.name, :cus_total => customer_total }   @grand_total += customer_total end

Collectives™ on Stack Overflow

Optimizing db queries in Rails 4

view index.rb

customers_controller.rb

model orders.rb

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

view index.rb

customers_controller.rb

model orders.rb

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related