Hi, I want to preload all categories subcategories.
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],
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)
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.
@dokicro: It’s not supported in
Ecto, but you can create your own
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
@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:
- Get all categories from database