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