Create Foreign Key Relationship Between Ecto Tables

Before You Begin

Installing Phoenix

This article assumes that you have installed the Phoenix web framework and all its dependencies correctly without errors. If you have not installed the Phoenix web framework please view the documentation here

---

Overview

This document walks you through the creation of a has_many and belongs_to table relationship through the creation of two tables named Item and Group.

To accomplish this you will create two tables in Phoenix using the HTML generator.

• Item(s)
• Group(s)

You will create a relationship between these two tables so that:

• A Group can have many Items
• Each Item can belong to 1 Group

Getting Started

In Phoenix, generate the two tables and related UI code using these commands:

mix phx.gen.html Items Item items name:string

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

Keep your eye on the command prompt. The command line will prompt you to add the resources / route for each table. Do what it says.

After updating the routes, run the migrate command.

 mix ecto.migrate

Item Table Migration

For the item table, you will create a migration file and add this field: group_id:references:groups. This is explained below.

To create a migration file, you CD into your app directory and run the migration creation command below. Replace "name-of-migration" with "items_update" or similar.

mix ecto.gen.migration name-of-migration

The migration file is created in this directory: app/priv/repo/migrations . Go to that directory and open the newly created migration file with your text editor. Update it to look like the following:

def change do
    alter table(:items) do    
      add :group_id, references(:groups) # create association
    end
  end

Run the migration:

mix ecto.migrate

Change Group Table Schema

Update Group schema to look like the code below.

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

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

Change Item Schema

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

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

You will need to incorporate “preload” so that you can submit data. Go to app/lib/app/items.ex file and update it like this:

  def list_items do
    Repo.all(Item)
    |> Repo.preload([:group]) # Preload
  end

Go to app/lib/app/groups.ex file and update it like this:

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

You now write code that assigns a Item to a Group.

Create Table Entries

Open your terminal in your app directory to write this code.

App.Groups.create_group(%{name: "Group of items"})
App.Items.create_item(%{name: "an item"})

You can query the Groups and Items and view the result.

App.Groups.list_groups

App.Items.list_items

The result looks like this:

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

When you query Items they look like this:

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

Assign an Existing Item to a Group

Open the terminal in your app directory to write this code.

group = App.Groups.get_group!(1)
item = App.Items.get_item!(1)
App.Items.update_item(item, %{group_id: group.id})

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

App.Groups.list_groups

Submit Item and Assign to a Group Association

This code creates a new item "on the fly" and assign it to the Group that has an ID of 1.

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

Aside

Rendering Nested Data in Templates

To render this form of nested data in Templates you use the inputs-for construct.

https://hexdocs.pm/phoenix_live_view/Phoenix.Component.html#inputs_for/1

Example:

<.inputs_for :let={f_nested} field={f[:nested]}>
    <.input type="text" field={f_nested[:name]} />
</.inputs_for>