Fast way to sort a model by count of child's child

Question

I currently have the following models: MinorCategory > Product > Review On a view, I show the 12 MinorCategories that have the most reviews. This view is very slow to respond, and I think it is a problem with how I do the query.

Here is my current code:

class MinorCategory < ActiveRecord::Base
  has_many :products
  has_many :reviews, through: :products
  ...
  def count_reviews
    self.reviews.count
  end
  ...
end

class Review < ActiveRecord::Base
  belongs_to :product, touch: true
  ...
end

class HomeController < ApplicationController
  @categories = MinorCategory.all.sort_by(&:count_reviews).reverse.take(12)
end

So that is basically it. In the view itself I go through each @categories and display a few things, but the query in the controller is what seems to be slow. From SkyLight:

SELECT COUNT(*) FROM "reviews" INNER JOIN "products" ON "reviews"."product_id" = "products"."id" WHERE "products"."minor_category_id" = ? ... avg 472ms

I am not good with sql or active record, and still pretty new to Ruby on Rails. I've spent a couple hours trying other methods, but I can not get them to work so I thought I would check here.

Thank you in advance to anybody that has a moment.

score 0 · Answer 1 · edited May 23 '17 at 12:16

You need some basic SQL knowledge to better understand how database queries work, and how to take advantage of a DBMS. Using ActiveRecord is not an excuse to not learn some SQL.

That said, your query is very inefficient because you don't use the power of the database at all. It's a waste of resources both on the Ruby environment and on the database environment.

The only database query is

MinorCategory.all

which extracts all the records. This is insanely expensive, especially if you have a large number of categories.

Moreover, self.reviews.count is largely inefficient because it is affected by the N+1 query issue.

Last but not least, the sorting and limiting is made in the Ruby environment, whereas you should really do it in the database.

You can easily obtain a more efficient query by taking advantage of the database computation capabilities. You will need to join the two tables together. The query should look like:

SELECT
    minor_categories.*, COUNT(reviews.id) AS reviews_count
FROM
    "minor_categories" INNER JOIN "reviews" ON "reviews"."minor_category_id" = "minor_categories"."id"
GROUP BY
    minor_categories.id
ORDER BY
    reviews_count DESC
LIMIT 10

which in ActiveRecord translates as

categories = MinorCategory.select('minor_categories.*, COUNT(reviews.id) AS reviews_count').joins(:reviews).order('reviews_count DESC').group('minor_categories.id').limit(10)

You can access a single category count by using reviews_count

# take a category 
category = categories[0]
category.reviews_count

Another approach that doesn't require a JOIN would be to cache the counter in the category table.

Fast way to sort a model by count of child's child

1 Answers1