Ecto Association with two objects of same class

Question

I'm trying to define a Game module that should have an associated Team linked to team_a_id column and another associated Team linked to team_b_id column (both should match id column on teams table).

So far it looks like this:

defmodule MyApp.Store.Game do
  use Ecto.Schema
  import Ecto.Changeset

  schema "games" do
    field :date, :utc_datetime
    field :title, :string

    has_many :game_gets, MyApp.Store.GameBet

    belongs_to :team_a, MyApp.Store.Team
    belongs_to :team_b, MyApp.Store.Team

    timestamps()
  end

  @doc false
  def changeset(game, attrs) do
    game
    |> cast(attrs, [:title, :date, :team_a_id, :team_b_id])
    |> validate_required([:title, :date])
  end
end

This is the migration file:

defmodule MyApp.Repo.Migrations.CreateGames do
  use Ecto.Migration

  def change do
    create table(:games) do
      add :title, :string
      add :date, :utc_datetime
      add :team_a_id, references(:teams, on_delete: :nothing)
      add :team_b_id, references(:teams, on_delete: :nothing)

      timestamps()
    end

    create index(:games, [:team_a_id])
    create index(:games, [:team_b_id])
    create unique_index(:games, [:title])
  end
end

Team module is defined like this:

defmodule MyApp.Store.Team do
  use Ecto.Schema
  import Ecto.Changeset

  schema "teams" do
    field :image_src, :string
    field :name, :string

    timestamps()
  end

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

Migration file is pretty standard:

defmodule MyApp.Repo.Migrations.CreateTeams do
  use Ecto.Migration

  def change do
    create table(:teams) do
      add :name, :string
      add :image_src, :string

      timestamps()
    end
  end
end

But up to this point, whenever I try to create an new Game:

{:ok, game} =
      attrs
      |> Enum.into(%{
        date: ~U[2022-07-31 15:03:00Z],
        title: "red_devils_vs_bold_guys_21092022",
        team_a_id: team_a.id,
        team_b_id: team_b.id
      })
      |> MyApp.Store.create_game()

I get the following error:

     ** (Postgrex.Error) ERROR 42703 (undefined_column) column "team_a_id" of relation "games" does not exist
     
         query: INSERT INTO "games" ("date","team_a_id","team_b_id","title","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6) RETURNING "id"

*Just in case anyone wonders, "team_a_id" column does exist:

tourney_dev=# SELECT * FROM games;
 id | title | date | team_a_id | team_b_id | inserted_at | updated_at 
----+-------+------+-----------+-----------+-------------+------------
(0 rows)

Answer 1

To formalize my comment into an example, I would rework the definition into one that uses has_one instead of belongs_to. Consider something like the following:

defmodule MyApp.Store.Team do
  use Ecto.Schema
  import Ecto.Changeset

  schema "teams" do
    field :image_src, :string
    field :name, :string

    timestamps()
  end

  # ...
end

defmodule MyApp.Store.Game do
  use Ecto.Schema
  import Ecto.Changeset

  schema "games" do
    field :date, :utc_datetime
    field :title, :string


    timestamps()
  end

  schema "games" do
    field :date, :utc_datetime
    field :title, :string
    field :team_a_id :integer
    field :team_b_id :integer
    timestamps()

    has_one :team_a, MyApp.Store.Team, foreign_key: :id, references: :team_a_id
    has_one :team_b, MyApp.Store.Team, foreign_key: :id, references: :team_b_id
  end
  # ...
end

The above definitions work, e.g. in iex I can do the following (verified/tested):

# use changesets to insert:

iex> MyApp.Store.Team.changeset(%Foo.Team{}, %{name: "Alpha"}) |> MyApp.Repo.insert()
iex> MyApp.Store.Team.changeset(%Foo.Team{}, %{name: "Beta"}) |> MyApp..Repo.insert()

# Or pass a valid struct to Repo.insert:
iex> %MyApp.Store.Game{title: "Showdown", team_a_id: 1, team_b_id: 2} |> MyApp.Repo.insert()

I think you need to show us what your MyApp.Store.create_game/1 function is doing.

Remember, with Ecto (or any database abstraction tool), defining the relations is only useful when it's actually useful. If you're not querying the database using the abstraction, then it can save time to just write regular old SQL queries.