CODEWITHSUNDEEP
ruby-on-rails
sscirrus
sscirrus

Reputation: 56709

Collect many 'counts' in one query?

I need to do the following:

<% for customer in @customers do %>
  <%= customer.orders.count %>
<% end %>

This strains the server, creating n queries, where n = number of customers.

How can I load these counts along with my customers in one query? Thanks.

Upvotes: 1

Views: 169

Answers (1)

Chris Heald
Chris Heald

Reputation: 62638

You could use an eager join:

@customers = Customers.paginate :page => 1, :per_page => 20, :include => [:orders]

By specifying the :include parameter to the join, the orders will be preloaded, preventing the n+1 problem. You can then use customer.orders.length.

If loading all those orders is too memory-intensive, then you should explore counter_cache. This is designed to keep a count of a model on an associated model:

class Order
  belongs_to :customer, :counter_cache => true
end

This will increment and decrement a orders_count field on the owning customer record when orders are added or removed from the associaition.

If you don't want to use the counter_cache, you'll need custom finder SQL which joins the orders table and groups on orders.customer_id, and then selects the count as an extra field. This will not perform nearly as well as the counter cache, though.

Upvotes: 2

Related Questions