I needed to figure out a way to recursively preload child records from a many_to_many relationship with Ecto, but all the discussions I saw were limited to hierarchical, self-join scenarios. This seemed very doable with a recursive CTE, though, so I got to work.
I’m new to Elixir, so I might’ve missed a better solution. But I went with passing a preloading function to Ecto.Query.preload/3.
The recursive CTE turns the simple many-to-many join table into a (partial) closure table. Then, within the preload function, I’m querying the CTE for all the child records and building out the fully nested Ecto structs.
That’s the version of the solution that I put into a gist to share here. But, for my actual use case, I took the extra step of creating a recursive view for the full closure table. Then I made a separate Ecto schema that models the rows of the closure table as Edges in the graph. This leaves the option of later making a materialized view of the closure table if performance requires it.
At some point, I hope to do a proper blog post about this. But in the meantime, I wanted to get a solution out there, in case someone else has a similar use case.
