Skip to content

Instantly share code, notes, and snippets.

@awright415
Created June 27, 2021 16:23
Show Gist options
  • Save awright415/7e00037cfc774a15ff0af53d8649c365 to your computer and use it in GitHub Desktop.
Save awright415/7e00037cfc774a15ff0af53d8649c365 to your computer and use it in GitHub Desktop.
Recursively Preloading Children from Join Table w/ Elixir & Ecto
# Use a recursive CTE to turn the join table into a (partial) closure table,
# then join all of the child records for the provided parent(s)
query = """
WITH RECURSIVE traversal(depth, parent_id, child_id) AS (
SELECT
1 depth,
parent_id,
child_id
FROM rules_rules
WHERE parent_id = ANY($1)
UNION
SELECT
t.depth + 1,
r.parent_id,
r.child_id
FROM traversal t
JOIN rules_rules r ON r.parent_id = t.child_id
)
SELECT
t.parent_id,
r.*
FROM traversal t
JOIN rules r ON t.child_id = r.id
ORDER BY depth DESC
"""
def preload_descendants(ids) do
with encoded <- Enum.map(ids, &Ecto.UUID.dump!/1),
{:ok, result} <- Ecto.Adapters.SQL.query(Repo, query, [encoded]),
[ _ | columns] <- result.columns
do
graph = result.rows
|> Enum.map(&instantiate_rules(columns, &1))
|> Enum.reduce(%{}, &nest_descendants/2)
for id <- ids, child <- graph[id], do: {id, child}
end
end
defp instantiate_rules(columns, [parent_id | attrs]) do
parent_id = Ecto.UUID.cast!(parent_id)
rule = Repo.load(Rule, {columns, attrs})
{parent_id, rule}
end
defp nest_descendants({parent_id, %{id: child_id} = child}, acc) do
case acc do
%{^parent_id => siblings, ^child_id => children} ->
Map.put(acc, parent_id, [%Rule{child | children: children} | siblings])
%{^child_id => children} ->
Map.put(acc, parent_id, [%Rule{child | children: children}])
%{^parent_id => siblings} ->
Map.put(acc, parent_id, [child | siblings])
%{} ->
Map.put(acc, parent_id, [child])
end
end
###
Repo.get(Rule, "2bbe6138-d81c-4076-be2b-1339834723b4") |> Repo.preload(children: &preload_descendants/1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment