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

Answer 1

includes is typically used for eager loading another table. In this case you already have access to the table you're searching through, categories.

What you could do is build out a Hash/Dictionary of categories which would require one call to your categories table to generate the hash.

category_hash = {}
Category.where(user_id: user.id).each do |category|
  category_hash[category.id] = {}
  # put whatever data you want to reference here
  category_hash[category.id][:parent_id] = category.parent_id
  category_hash[category.id][:name] = category.name
end

Then in referencing your categories as category_hash[:some_id] will give you whatever data you want to store in your hash...with O(1) time and no additional db queries.

Answer 2

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