Count has_many relationships

Question

I have 3 models: Import, Product, Values. An import has_many products. A product has_many values.

I want to find the product with the highest values count and the sum of all values for all those products. My current method is very expensive.

@import = Import.find(params[:id])
@products = @import.products
@max_product = @products.max_by{|product| product.values.count}
@values_count = @products.sum{|product| product.values.count}

How can I make this more efficient ?

Answer 1

You can make use ":counter_cache", it finds the number of belonging objects more efficient.

class Book < ApplicationRecord
 belongs_to :author, counter_cache: true
end
class Author < ApplicationRecord
 has_many :books
end

Asking for the value of @author.books.size requires making a call to the database to perform a COUNT(*) query. To avoid this call, you can add a counter cache, with this declaration, Rails will keep the cache value up to date, and then return that value in response to the size method.

Reference : http://guides.rubyonrails.org/association_basics.html

Answer 2

I would suggest you go with counter cache for this scenario. If you don't regularly update the values of a product, then use counter cache.

You can combine the three joins, group_by and select(count()) if you want to aggregate results.

If you regularly do update the values, then you can write your own single iteration to find max and sum. To avoid n+1 query issues, use includes to make your code work faster.

Answer 3

One solution would be something like:

@import = Import.join(product: : values).select('import.*, 'sum(product.values.count as values_count').find(params[:id])
@values_count = @import['values_count']
@max_product = import.products.join(:values).order('values.count desc').first