CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-4
Tolsto
Tolsto

Reputation: 1428

Optimizing db queries in Rails 4

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?

Upvotes: 1

Views: 616

Answers (2)

hrnnvcnt
hrnnvcnt

Reputation: 944

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.

Upvotes: 2

Utsav Kesharwani
Utsav Kesharwani

Reputation: 1745

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>

Upvotes: 1

Related Questions