Ecto on_replace delete from the wrong table


I have this self referential schema (Node)

schema "nodes" do
  field :name, :string

  field :type, :string
  field :params, :map
  field :index, :integer, default: 0

  belongs_to :tree, Tree

  belongs_to :parent, __MODULE__
  has_many :children, __MODULE__, foreign_key: :parent_id, on_replace: :delete


def changeset(page, attrs) do
  |> cast(attrs, [:name, :parent_id, :type, :params, :index])
  |> validate_required([:name, :type, :params, :index])
  |> validate_length(:name, min: 3, max: 200)
  |> validate_number(:index, greater_than_or_equal_to: 0)
  |> cast_assoc(:children, with: &__MODULE__.changeset/2)

and that schema belongs to another schema (Tree)

schema "trees" do
  field :name, :string

  has_one :root, Node, foreign_key: :space_id


def changeset(tree, attrs) do
  |> cast(attrs, [:id, :name])
  |> validate_required([:name])
  |> cast_assoc(:root, with: &Node.changeset/2, required: true)

And when I do update, I receive a full payload (for a tree) from the client, then I have a query to get the existing tree + its root + all of the children down to the leaves.

def update_tree(%{"id" => id} = tree) do
  with {:ok, existing} <- get_tree(id) do
    |> Tree.changeset(tree)
    |> Repo.update()

It works fine for creating and updating tree + root + leaves nodes. But it gives a weird query when I try to replace all children of the root node with a whole new set of nodes.

     ** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "children" does not exist
         query: DELETE FROM "children" WHERE "id" = $1

Why does it try to delete from children instead of nodes table? :thinking: Did I do anything wrong with my setup?


It would be a good idea to post the exact Ecto query you are running that generates the error.

Also for your existing examples there are variables appearing that don’t seem to be defined anywhere. For example page here

def update_tree(%{"id" => id} = tree) do
  with {:ok, existing} <- get_tree(id) do
    |> Tree.changeset(page)
    |> Repo.update()

In general, the best way to get help is to provide minimal reproducible examples. It makes it more likely you will get a correct response quickly. And it makes it a more pleasant experience for those trying to help you.

1 Like

this does not look correct…

the on_replace should go on the belongs_to part I think

1 Like

Updated the code, made a typo there (page should be tree)

The query I’m trying to run is in the update_tree function, not sure what else should I post to make it clearer.

I tried to move it to the belongs_to put it gives

** (RuntimeError) you are attempting to change relation :children of
App.Node but the `:on_replace` option of this relation
is set to `:raise`.

Found the problem (or at least I think)

get_tree function does a recursive query to get all the children of a root node. And it obviously returns a flat list of all the rows. But I want to assign those rows to proper parent in order to form the correct tree structure.

But the Ecto.Schema.Metadata has its source set to children instead of nodes which results in the incorrect table when forming a DELETE query. After digging through ecto source code, I found the problem is about the CTE table name. Apparently, if I set a CTE table name, it will be used as the source for all the rows, which makes sense.

And this is how I wrote the recursive query

defp get_all_nodes(root_id) do
  initial = where(Node, [r], == ^root_id)
  recursion = join(Node, :inner, [r], c in "children", on: == r.parent_id)
  children = union_all(initial, ^recursion)

  {"children", Node} # <-- this is the problem
  |> recursive_ctes(true)
  |> with_cte("children", as: ^children)
  |> where([r], != ^root_id)
  |> order_by([r], asc: r.index)
  |> Repo.all()

With the above query, the source of the schema metadata (or each child node) is set to “children”. To fix it, just pass the module instead of a tuple with a cte table name.

Node |> recursive_ctes(true)