View source for Create Foreign Key Relationship Between Ecto Tables

'''Before You Begin'''

{{Phoenix-Installation-Required}}

==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:
<source>
mix phx.gen.html Items Item items name:string

mix phx.gen.html Groups Group groups name:string
</source>

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.

 <source> mix ecto.migrate </source>


'''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.

<source>
mix ecto.gen.migration name-of-migration   
</source>

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:

<source>
def change do
    alter table(:items) do    
      add :group_id, references(:groups) # create association
    end
  end
 </source>
  
Run the migration:

<source>
mix ecto.migrate
</source>
  
'''Change Group Table Schema'''


Update Group schema to look like the code below. 

<source>
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


</source>

'''Change Item Schema'''

<source>

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
</source>

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:

<source>
  def list_items do
    Repo.all(Item)
    |> Repo.preload([:group]) # Preload
  end
 </source>
 
Go to app/lib/app/groups.ex file and update it like this:

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

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.

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

</source>

You can query the Groups and Items and view the result.
<source>
App.Groups.list_groups

App.Items.list_items
</source>

The result looks like this:

<source>

 %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]
  }
  
  </source>
When you query Items they look like this:

<source>
%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]
  }
</source>


== Assign an Existing Item to a Group ==

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

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

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.

<source>
App.Groups.list_groups

</source>

==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.

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

</source>

==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:'''

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