ruby-on-rails - How to avoid N + 1 queries with self-referential table in Rails

Question

How to I correctly use includes to avoid N + 1 queries in this situation:

I have a set of categories that can be nested (i.e., they form a tree). For example:

• Teaching
  • Course 1
    • Office Hours
    • Lecture
  • Course 2
    • Office Hours
    • Lecture
• Research
  • Project 1
  • Project 2
• Service

To set up this hierarchy, each Category record has parent_id as a foreign key.

This is my Rails model:

class Category < ApplicationRecord
  belongs_to :user
  belongs_to :parent, class_name: "Category", optional: true
  has_many :children, class_name: "Category", foreign_key: "parent_id"
end

I access all the categories for a given user using

@categories = Category.includes(:children).where(user_id: user.id)

However, every call to @categories[i].children generates a new query.

How do I correctly use includes so that I can access each category's child categories without additional queries.

(I also tried @categories = Category.where(user_id: user.id).includes(:children) with no change in behavior.)

标签： ruby-on-railsactiverecordselect-n-plus-1

Answer 1

You must use joins too, so you're able to use the relationship between categories and the so called children_categories:

Category.includes(:children).joins(:children).where(user_id: <user_id>)
# SELECT "categories"."id" AS t0_r0,
#        ...
#        "children_categories"."id" AS t1_r0,
#        ...
# FROM "categories"
# INNER JOIN "categories" "children_categories"
# ON "children_categories"."parent_id" = "categories"."id"
# WHERE "categories"."user_id" = $1

Otherwise you'll see what I think is your problem now:

Category.includes(:children).where(user_id: <user_id>)
# SELECT "categories".* FROM "categories" WHERE "categories"."user_id" = $1
# SELECT "categories".* FROM "categories" WHERE "categories"."parent_id" IN ($1, $2, ...)