CODEWITHSUNDEEP
postgresqlelixirphoenix-frameworkecto
user2939475
user2939475

Reputation: 11

Self referencing association in Phoenix using Ecto

So I've just started fiddeling around with Phoenix, and Elixir. So I have reached the point were I am trying to get a working rest-api endpoint to work with a prerequisite JSON.

So I have this module:

defmodule MyApp.Housing.Part do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, :integer, []}
  schema "parts" do
    field :level, :integer
    field :title, :string
    belongs_to :parent, MyApp.Housing.Part
    has_many :children, MyApp.Housing.Part, foreign_key: :parent_id
    timestamps()
  end

  def changeset(part, params \\ %{}) do
    part
    |> cast(params, [:title, :level, :id, :parent_id])
    |> put_assoc(:children, required: false)
    |> put_assoc(:parent, required: false)
    |> validate_required([:title, :level, :id])
  end
end

And the module in which the table is created

 defmodule MyApp.Repo.Migrations.CreateParts do
  use Ecto.Migration

  def change do
    create table(:parts, primary_key: false) do
      add :id, :integer, primary_key: true
      add :title, :string
      add :level, :integer
      add :parent_id, references(:parts)
      add :children, references(:parts)
      timestamps()
    end

    create index(:parts, [:children])
    create index(:parts, [:parent_id])
  end
end

The inteded functionallity is for a part to be able to have multiple children but only one parent. And these are defined in a JSON like this:

{"id": 10,
    "title": "Matt",
    "level": 0,
    "children": [],
    "parent_id": null}

So my problem is the following:

I might take the wrong approach here but would gladly accept any help.

Upvotes: 1

Views: 1847

Answers (2)

percygrunwald
percygrunwald

Reputation: 433

As mentioned by @steve-pallen, it's not necessary to store any references to children in the database. Determining whether or not a Part is a parent or child, as well as which Parts are its children or which Part is its parent can be determined fully by the parent_id field.

You described in your question that each Part "can only have one parent, but multiple children". It's not explicit in your question how many levels the relationship allows: i.e. can a Part be both a parent and a child? In which case, there would be potentially infinite levels of nesting:

part1
  |- part2
    |- part3
      |- part4

In this case, part1 is the parent of part2, part2 is itself the parent of part3, etc. I'm going to assume for my answer that there is no limit to the amount of nesting.

Given this case, your schema definition is 100% correct:

belongs_to :parent, MyApp.Housing.Part
has_many :children, MyApp.Housing.Part, foreign_key: :parent_id

I think the primary issue is with your changeset function. Remember that with put_assoc/3, it's expected that all the models referenced by parent and children already exist in the DB (see the docs for cast_assoc/3). For simplicity I suggest that you don't use put_assoc or cast_assoc and instead manage each model in isolation. If you change your changeset function to this (I've removed id since it's not necessary):

def changeset(part, params \\ %{}) do
  part
  |> cast(params, [:title, :level, :parent_id])
  |> validate_required([:title, :level])
end

Then you can build the nested relationship I showed above by doing 4 inserts in isolation (much easier to reason about, and probably more in line with how you'd handle DB updates from a form or script):

part1 = 
  MyApp.Housing.Part.changeset(%MyApp.Housing.Part{}, %{title: "part1", level: 0, parent_id: nil})
  |> Repo.insert!()

part2 = 
  MyApp.Housing.Part.changeset(%MyApp.Housing.Part{}, %{title: "part2", level: 0, parent_id: part1.id})
  |> Repo.insert!()

part3 = 
  MyApp.Housing.Part.changeset(%MyApp.Housing.Part{}, %{title: "part3", level: 0, parent_id: part2.id})
  |> Repo.insert!()  

part4 = 
  MyApp.Housing.Part.changeset(%MyApp.Housing.Part{}, %{title: "part4", level: 0, parent_id: part3.id})
  |> Repo.insert!()

Assuming we want to get part2, we can load it along with its parent and children like this:

part2 = Repo.preload(part2, [:parent, :children])
# part2.parent == %MyApp.Housing.Part{title: "part1", ...}
# part2.children == [%MyApp.Housing.Part{title: "part3", ...}]

Hope this helps!

Upvotes: 1

Steve Pallen
Steve Pallen

Reputation: 4507

The first thing you need to fix is the migration. You don't want the children field since that is a has_many relationship and is handled by the parent_id field in the children. It should look like this:

 defmodule MyApp.Repo.Migrations.CreateParts do
  use Ecto.Migration

  def change do
    create table(:parts, primary_key: false) do
      add :id, :integer, primary_key: true
      add :title, :string
      add :level, :integer
      add :parent_id, references(:parts)

      timestamps()
    end

    create index(:parts, [:parent_id])
  end
end

Handling the children in the changeset depends on a couple things.

  • What does the incoming payload look like when there are children?
  • Will there be new children in the children list or just existing children?

Upvotes: 0

Related Questions