+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Ruby (https://0day.red/Forum-Ruby)
+--- Thread: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? (/Thread-Rails-how-to-avoid-the-quot-N-1-quot-queries-for-the-totals-count-size-counter-cache-in-associations)
Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - dianemariep - 07-19-2023
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?
RE: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - thanhfdpfrejuyl - 07-19-2023
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
RE: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - unrelievedly195349 - 07-19-2023
This might help you.
@childrens_count = @cinema.childrens.joins(:movies).group("movies.children_id").count.to_a
RE: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - curcas254673 - 07-19-2023
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]
RE: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - zambrano431 - 07-19-2023
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.
RE: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - cystoparalysis691521 - 07-19-2023
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.
RE: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - benedix678 - 07-19-2023
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 %>
RE: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - prosencephalic557298 - 07-19-2023
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]
RE: Rails, how to avoid the "N + 1" queries for the totals (count, size, counter_cache) in associations? - amongst196 - 07-19-2023
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.