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

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?

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

This might help you.

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

Based on [sarav answer][1] 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




[1]:

[To see links please register here]

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:



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.

[To see links please register here]

) but I didn't like their interface/DSL. I was looking for something (syntactically) similar to active records `preload` (

[To see links please register here]

) 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.

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:

[To see links please register here]

please feel free to ask if there is any concern.

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 %>

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...][1]

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


[1]:

[To see links please register here]

You can use [includes](

[To see links please register here]

) 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.