Create Foreign Key Relationship Between Ecto Tables

This page is in progress

My goal is to create two html generated tables in Phoenix.

• Testbed(s)
• Group(s)

I want to create a relationship between these two tables so that:

• A Group can have many Testbeds
• Each Testbed can belong to 1 Group
• I intend to write front end code that presents this relationship to the user.

Steps

In Phoenix I generated the two tables and related UI code using these commands:

mix phx.gen.html Testbeds Testbed testbeds name:string

mix phx.gen.html Groups Group groups name:string

The command line prompted me to add the resources / route for both of them. I did what it said.

I ran:

 mix ecto.migrate

Testbed Migration

For testbeds, I create a migration file and add this field: group_id:references:groups.

To create a migration file you CD into your app directory and run the migration creation code. In the code below replace "name-of-migration" with "test_update" or similar.

mix ecto.gen.migration name-of-migration

def change do
    alter table(:testbeds) do    # customize to your code
      add :group_id, references(:groups)
    end
  end

Change Group Schema I update Group schema. Changes in Comments

defmodule App.Groups.Group do
  use Ecto.Schema
  import Ecto.Changeset
  
  alias App.Testbeds.Testbed   # Alias!
  schema "groups" do
    field :name, :string
    has_many :testbeds, Testbed    # Has many
    timestamps()
  end

  @doc false
  def changeset(group, attrs) do
    group
    |> cast(attrs, [:name])
    |> validate_required([:name])
  end
end

Change Testbed Schema

defmodule App.Testbeds.Testbed do
  use Ecto.Schema
  import Ecto.Changeset
  alias App.Groups.Group      # Alias
  schema "testbeds" do
    field :name, :string
    belongs_to :group, Group    # Belongs to
    timestamps()
  end

  @doc false
  def changeset(testbed, attrs) do
    testbed
    |> cast(attrs, [:name, group_id])   # group_id
    |> validate_required([:name])
  end
end

I read that I needed to incorporate “preload” so that I can submit data. So I did this:

  def list_testbeds do
    Repo.all(Testbed)
    |> Repo.preload([:group])
  end

and this

  def list_groups do
    Repo.all(Group)
     |> Repo.preload([:testbeds])
  end

When I create Groups and Testbeds and I query them, the result looks like this:

 %App.Groups.Group{
    __meta__: #Ecto.Schema.Metadata<:loaded, "groups">,
    id: 3,
    name: "FUNK",
    testbeds: [],
    inserted_at: ~N[2023-09-27 18:13:15],
    updated_at: ~N[2023-09-27 18:13:15]
  }

When I query Testbeds they now look like this:

%App.Testbeds.Testbed{
    __meta__: #Ecto.Schema.Metadata<:loaded, "testbeds">,
    id: 1,
    name: "Bloop",
    group_id: nil,
    group: nil,
    inserted_at: ~N[2023-09-27 14:04:15],
    updated_at: ~N[2023-09-27 18:22:23]
  }

I now need to know how to write code that assigns a Testbed to a Group.

Create a Group

App.Groups.create_group(%{name: "OINK"})

Submit Testbed and Assign to a Group Association

This code will create a new testbed and assign it to the Group that has an ID of 1.

group = App.Groups.get_group!(1)
thing = Ecto.build_assoc(group, :testbeds, name: "DUMB")
alias App.{Repo}
Repo.insert(thing)

Run this code to view the result. You will see the group with a field name testbeds that is assigned a List. The List contains a Testbed struct.

App.Groups.list_groups

Assign an Existing Testbed to a Group

group = App.Groups.get_group!(1)
testbed = App.Testbeds.get_testbed!(1)
App.Testbeds.update_testbed(testbed, %{group_id: group.id})