如何在Ecto中递归预加载分类的所有子分类？

Got it, let's fix this recursive subcategory preloading issue! Your current query only grabs the first level of subcategories, but we can solve this with two main approaches—one database-side (most efficient) and one Elixir-side (simpler for small datasets).

Approach 1: Recursive CTE (Database-Side, Recommended) #

The best way to fetch all nested levels in a single query is using a recursive Common Table Expression (CTE). This lets the database handle the recursion, avoiding N+1 query problems and keeping things fast even for deep category trees.

First, your existing schema is already set up correctly (we'll tweak the association name for Elixir conventions):

schema "categories" do
  field :name, :string
  has_many :sub_categories, Category, foreign_key: :parent_id
end

Now here's the recursive query to load all nested subcategories:

def get_root_categories_with_all_subs do
  Repo.all(
    from c in Category,
      # Define the recursive CTE
      with_cte("recursive_categories", as: [
        # Base case: get all root categories (no parent)
        root <- from c in Category, where: is_nil(c.parent_id), select: c,
        # Recursive case: join subcategories to their parent categories from the CTE
        recursive sub <- from c in Category, join: p in "recursive_categories", on: c.parent_id == p.id, select: c
      ]),
      # Join and preload subcategories for every category in the CTE
      left_join: sub in assoc(c, :sub_categories),
      preload: [sub_categories: sub],
      # Only include categories that are part of our recursive tree
      where: c.id in subquery(from rc in "recursive_categories", select: rc.id)
  )
end

When you run this, each root category's sub_categories will contain its direct children, and each of those children will have their own sub_categories populated—all the way down to the leaf nodes.

Approach 2: Elixir Recursive Function (Simpler, Less Efficient) #

If you prefer avoiding SQL CTE syntax, you can use an Elixir recursive function to load each level of subcategories. Note: This will run one query per level of your category tree, so it's better for small or shallow trees.

def load_nested_subcategories(categories) do
  # Preload all direct subcategories for the current list of categories
  preloaded_categories = Repo.preload(categories, :sub_categories)
  
  # Recursively load subcategories for each child
  Enum.map(preloaded_categories, fn category ->
    %{category | sub_categories: load_nested_subcategories(category.sub_categories)}
  end)
end

# Usage:
root_categories = Repo.all(from c in Category, where: is_nil(c.parent_id))
root_categories_with_all_subs = load_nested_subcategories(root_categories)

Key Notes #

• CTE is better for performance: It fetches all data in a single round trip to the database, which is critical for large or deep category trees.
• Elixir recursion is simpler: Great for small datasets or when you want to avoid writing SQL-specific code.

内容的提问来源于stack exchange，提问作者DokiCRO