Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations?

Question

I have a these models:

class Children < ActiveRecord::Base
    has_many :tickets
    has_many :movies, through: :tickets
end


class Movie < ActiveRecord::Base
    has_many :tickets
    has_many :childrens, through: :tickets
    belongs_to :cinema
end


class Ticket < ActiveRecord::Base
    belongs_to :movie, counter_cache: true
    belongs_to :children
end


class Cinema < ActiveRecord::Base
    has_many :movies, dependent: :destroy
    has_many :childrens, through: :movies
end

What I need now is in the page of "Cinemas" I wanna print the sum (count, size?) of the childrens just for the movies of that cinemas, so I wrote this:

• in the cinemas_controller.rb:

@childrens = @cinema.childrens.uniq

• in the cinemas/show.html.erb:

<% @childrens.each do |children| %><%= children.movies.size %><% end %>

but obviously I have bullet gem that alert me for Counter_cache and I don't know where to put this counter_cache because of different id for the movie.

And also without the counter_cache what I have is not what I want because I want a count for how many childrens in that cinema taking them from the tickets from many days in that cinema.

How to?

UPDATE

If in my view I use this code:

<% @childrens.each do |children| %>
  <%= children.movies.where(cinema_id: @cinema.id).size %>
<% end %>

gem bullet don't say me anything and every works correctly.

But I have a question: this way of querying the database is more heavy because of the code in the views?

Answer 1

This might help you.

@childrens_count = @cinema.childrens.joins(:movies).group("movies.children_id").count.to_a

Answer 2

You can use includes to load all associations ahead of time. For example:

@childrens = @cinema.childrens.includes(:movies).uniq

This will load all of the children's movies in the controller, preventing the view from needing access to the database in your loop.

Answer 3

You might agree, that the number of movies belongs to a child equals the number of tickets they bought. That's why you could just cache the number of tickets and show it on the cinemas#show. You can even create a method to make it more clear.

class Children < ActiveRecord::Base
  has_many :tickets
  has_many :movies, through: :tickets

  def movies_count
    tickets.size
  end
end

class Ticket < ActiveRecord::Base
  belongs_to :movie, counter_cache: true
  belongs_to :children, counter_cache: true
end

class Movie < ActiveRecord::Base
  belongs_to :cinema
  has_many :tickets
  has_many :childrens, through: :tickets
end

class Cinema < ActiveRecord::Base
  has_many :movies, dependent: :destroy
  has_many :childrens, through: :movies
end

And then:

<% @childrens.each do |children| %><%= children.tickets.size %><% end %>

Or

<% @childrens.each do |children| %><%= children.movies_count %><% end %>

But if you want to show the number of tickets for every movie, you definitely need to consider the following:

@movies = @cinema.movies

Then: <% @movies.each do |movie| %><%= movie.tickets.size %><% end %> Since you have belongs_to :movie, counter_cache: true, tickets.size won't make a count query. And don't forget to add tickets_count column. More about counter_cache...

P.S. Just a note, according to conventions we name a model as Child and an association as Children.

Answer 4

Actually is much more simpler than the remaining solutions

You can use lazy loading:

In your controller:

def index
  # or you just add your where conditions here
  @childrens = Children.includes(:movies).all 
 end

In your view index.hml.erb:

<% @childrens.each do |children| %>
  <%= children.movies.size %>
<% end %>

The code above won't make any extra query if you use size but if you use count you will face the select count(*) n + 1 queries

Answer 5

I wrote a little ActiveRecord plugin some time ago but haven't had the chance to publish a gem, so I just created a gist:

https://gist.github.com/apauly/38f3e88d8f35b6bcf323

Example:

# The following code will run only two queries - no matter how many childrens there are:
#   1. Fetch the childrens
#   2. Single query to fetch all movie counts
@cinema.childrens.preload_counts(:movies).each do |cinema|
  puts cinema.movies.count
end

---

To explain a bit more:

There already are similar solutions out there (e.g. https://github.com/smathieu/preload_counts) but I didn't like their interface/DSL. I was looking for something (syntactically) similar to active records preload (http://apidock.com/rails/ActiveRecord/QueryMethods/preload) method, that's why I created my own solution.

To avoid 'normal' N+1 query issues, I always use preload instead of joins because it runs a single, seperate query and doesn't modify my original query which would possibly break if the query itself is already quite complex.

Answer 6

In You case You could use something like this:

class Ticket < ActiveRecord::Base
    belongs_to :movie, counter_cache: true
    belongs_to :children
end
class Movie < ActiveRecord::Base
    has_many :tickets
    has_many :childrens, through: :tickets
    belongs_to :cinema
end
class Children < ActiveRecord::Base
    has_many :tickets
    has_many :movies, through: :tickets
end
class Cinema < ActiveRecord::Base
    has_many :movies, dependent: :destroy
    has_many :childrens, through: :movies
end


@cinema = Cinema.find(params[:id])
@childrens = Children.eager_load(:tickets, :movies).where(movies: {cinema_id: @cinema.id}, tickets: {cinema_id: @cinema.id})


<% @childrens.each do |children| %>
  <%= children.movies.count %>
<% end %>

Answer 7

Your approach using counter_cache is in right direction.

But to take full advantage of it, let's use children.movies as example, you need to add tickets_count column to children table firstly.

execute rails g migration addTicketsCountToChildren tickets_count:integer,

then rake db:migrate

now every ticket creating will increase tickets_count in its owner(children) by 1 automatically.

then you can use

<% @childrens.each do |children| %>
  <%= children.movies.size %>
<% end %>

without getting any warning.

if you want to get children count by movie, you need to add childrens_count to movie table:

rails g migration addChildrensCountToMovies childrens_count:integer

then rake db:migrate

ref:

http://yerb.net/blog/2014/03/13/three-easy-steps-to-using-counter-caches-in-rails/

please feel free to ask if there is any concern.

Answer 8

Based on sarav answer if you have a lot of things(requests) to count you can do:

in controller:

@childrens_count = @cinema.childrens.joins(:movies).group("childrens.id").count.to_h

in view:

<% @childrens.each do |children| %>
   <%= @childrens_count[children.id] %>
<% end %>

This will prevent a lot of sql requests if you train to count associated records