Updating rows returned by a recursive CTE in Ecto

I’ve posted this on the Elixir Slack, but thought it wise to ask here for broader visibility (and future adventurers):

Similarly to this unanswered post 5 years ago, but the background is that I have a table tags which is hierarchical - each tag has a parent_id column which links it to its parent tag. I have the following function in my Tags context to return the given tag and all of its ancestors in a flat list:

def list_ancestors(%Tag{id: id}) do
  parent_query = join(Tag, :inner, [t], p in "parent", on: p.parent_id == t.id)
  child_query = Tag |> where([t], t.id = ^id) |> union_all(^parent_query)

  {"parent", Tag}
  |> recursive_ctes(true)
  |> with_cte("parent", as: ^child_query)
  |> distinct(true)
  |> Repo.all()
end

So far, so good. This works and everyone is happy.

Except when you try to Repo.update/2 one of the rows returned by that query: each %Tag{} returned has "parent" as its Ecto.Schema.Metadata.source and so the update fails because that table doesn’t exist.

My workaround is to add the following line after the Repo.all/1 in order to reset the :source metadata to that of the %Tag{}, but I figure there’s gotta be a better way.

|> Enum.map(&Ecto.put_meta(&1, source: Tag.__schema__(:source)))

What am I missing?

Try joining the result with the original schema:

Tag
|> recursive_ctes(true)
|> with_cte("parent", as: ^child_query)
|> join(:inner, [tag], cte in "parent", on: tag.id == cte.id)
|> distinct(true)
|> Repo.all()

Assuming you were following the example from the docs

{"category_tree", Category}
|> recursive_ctes(true)
|> with_cte("category_tree", as: ^category_tree_query)

what happens is that you’re passing an Ecto.Queryable tuple protocol structure that lets you

reuse the same schema while specifying a separate “source” for the data.

7 Likes

That works perfectly, thank you!

1 Like