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.

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 "posts" do
		field :body, :string
		field :title, :string
		has_many :testbeds, Testbed    # Has many

		timestamps()
	end

	@doc false
	def changeset(post, attrs) do
		post
		|> cast(attrs, [:title, :body])
		|> validate_required([:title, :body])
	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])
    |> 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 query Groups they now look 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.

Submit Testbed with Group Association

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