Recursively load all sub categories

Hi, I want to preload all categories subcategories.

Example:

-> Sport
   -> Football
      -> Man
      -> Woman
   -> Handbal
      -> Man
      -> Woman

So far I managed to preload only first child here is the code I am using:

schema "categories" do
    has_many :subCategories, Category, foreign_key: :parent_id

------

Repo.all from category in Category,
      left_join: subCategories in assoc(category, :subCategories),
      preload: [subCategories: subCategories],
      where: is_nil(category.parent_id)

I found a way how to load all children categories but it makes too much SQL queries (http://tensiondriven.github.io/posts/recursively-load-self-referential-association-using-ecto)

1 Like

You want to model your datastructures as a tree. However, the data structure you’ve modeled in your database is a graph. This will indeed take a lot of effort to query all children from, and besides this it is possible to create loops, which will require a defensive algorithm implementation to not get stuck on during tree iteration.

But there are other ways to store trees in a database. A clever and easy-to-understand way is what ecto_materialized_path does: It stores the path back to the root node in the database, which means that you can query on (a bit of) this path to get the whole subtree from a given node onwards.

1 Like

@dokicro: It’s not supported in Ecto, but you can create your own SQL using with recursive statement. With that you can load all parent and children in only one query.

@Qqwy Thank you I will take a look at this :slight_smile:

@Eiji thank you this was my backup plan also…

@Qqwy what happens when I switch one category to another parent does all child nodes of that category updates automatically?

I have not tried it myself, but I expect that the make_child_of function that is explained on the GitHub page is the one you are looking for. I think it should create a list of changes that updates all children whose path starts with ‘old path of the parent’ to start with the new part of the parent.

So I decided to do this approach:

  1. Get all categories from database
  2. Sort them in subcategories with elixir :slight_smile: (in my case javascript since I am buidling SPA)