How to model (and seed) a tree-like structure in Ecto?

Questions / Help
1

I have two entities: User and Node. A user has multiple nodes. A node can have multiple nodes as well, so in the end, it can form a tree-like structure. I am not sure how to model this. Here is my approach:

Migrations:

#
# Create Users
#
create table(:users) do
  add :name, :string, null: false, size: 64
end

#
# Create Nodes
#
create table(:nodes) do
  add :name, :string, null: false, size: 64
  add :parent_id, references(:nodes, on_delete: :nothing)
  add :user_id, references(:users, on_delete: :nothing)
end

Schemas:

schema "users" do
  field :name, :string
  has_many :nodes, ScorecardBackend.Node
end

schema "nodes" do
  field :name, :string
  belongs_to :parent, Node
  has_many :children, Node
  belongs_to :user, User
end

And the seeding code looks like this:

admin = User.registration_changeset(%User{}, %{
  name: "admin"
})

node_names = [
  %{
    priority: "Financial Management",
    objectives: [
      "Increase Sales Revenue",
      "Improve Cost Structure",
      "Achieve Assets Optimization"
    ]
  },
  %{
    priority: "Business Alignment",
    objectives: [
      "Provide High Value",
      "Develop Close Partnership",
      "Provide Right Service"
    ]
  }
]

priorities = Enum.map(node_names, fn(p_item) ->

  p = Node.changeset(%Node{}, %{
    name: p_item.priority
  })

  objectives = Enum.map(p_item.objectives, fn(o_item) ->
    Node.changeset(%Node{}, %{
      name: o_item
    })
  end)

   Ecto.Changeset.put_assoc(p, :children, objectives)
end)

admin_with_priorities = Ecto.Changeset.put_assoc(admin, :priorities, priorities)

Repo.insert!(admin_with_priorities)

The problem is that when I look in the content of the database, the column nodes.parent_id is always empty:

id  |   name                  | parent_id
------------------------------------------
173 | Financial Management    | null
174 | Increase Sales Revenue  | null
... | ...                     | ...

What am I doing wrong?

(Sidenote: So far, Ecto, just like any other ORM seems to be to me a really bad tool for complex database structures with many relations. I am strongly considering going into raw SQL with manual conversions.)

1 Like
2

I think I figured that out. It was missing , foreign_key: :parent_id in here:

schema "nodes" do
 field :name, :string
  belongs_to :parent, Node
  has_many :children, Node, foreign_key: :parent_id
  belongs_to :user, User
end
3 Likes
3

If you want to have the deep organizational structure for you ecto models, consider using https://github.com/asiniy/ecto_materialized_path

1 Like
4

That looks sweet. Thank you!