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 SQL using with recursive statement. With that you can load all parent and children in only one query.
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.