Create Foreign Key Relationship Between Ecto Tables

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

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

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

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 will create 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)