axelclark

Add two foreign key columns that reference the same table

I’m building a fantasy sports app and I’m trying to model waivers. With a waiver, the fantasy team is adding a player and dropping a different player. I’m trying to set up my table to have an add column that contains a fantasy player id and a drop column that contains a fantasy player id. I’m having trouble getting my models to compile.

My migration is:

defmodule Ex338.Repo.Migrations.CreateWaiver do
  use Ecto.Migration

  def change do
    create table(:waivers) do
      add :status, :string
      add :fantasy_team, references(:fantasy_teams, on_delete: :nothing)
      add :add, references(:fantasy_players, on_delete: :nothing)
      add :drop, references(:fantasy_players, on_delete: :nothing)

      timestamps()
    end
    create index(:waivers, [:fantasy_team])
    create index(:waivers, [:add])
    create index(:waivers, [:drop])

  end
end

The result of the migration is:

                                      Table "public.waivers"
    Column    |            Type             |                      Modifiers                       
--------------+-----------------------------+------------------------------------------------------
 id           | integer                     | not null default nextval('waivers_id_seq'::regclass)
 status       | character varying(255)      | 
 fantasy_team | integer                     | 
 add          | integer                     | 
 drop         | integer                     | 
 inserted_at  | timestamp without time zone | not null
 updated_at   | timestamp without time zone | not null
Indexes:
    "waivers_pkey" PRIMARY KEY, btree (id)
    "waivers_add_index" btree (add)
    "waivers_drop_index" btree (drop)
    "waivers_fantasy_team_index" btree (fantasy_team)
Foreign-key constraints:
    "waivers_add_fkey" FOREIGN KEY (add) REFERENCES fantasy_players(id)
    "waivers_drop_fkey" FOREIGN KEY (drop) REFERENCES fantasy_players(id)
    "waivers_fantasy_team_fkey" FOREIGN KEY (fantasy_team) REFERENCES fantasy_teams(id)

I think that looks correct for what I’m trying to do.

Here are my schemas:

  schema "waivers" do
    field :status, :string
    belongs_to :fantasy_team, Ex338.FantasyTeam
    belongs_to :add, Ex338.FantasyPlayer, foreign_key: :add
    belongs_to :drop, Ex338.FantasyPlayer, foreign_key: :drop

    timestamps()
  end

 schema "fantasy_players" do   
    # additional fields removed
    has_many :adds, Ex338.Waiver, foreign_key: :add
    has_many :drops, Ex338.Waiver, foreign_key: :drop

    timestamps()
  end

I get the following error:

== Compilation error on file web/models/waiver.ex ==
** (ArgumentError) foreign_key :add must be distinct from corresponding association name
    lib/ecto/schema.ex:1181: Ecto.Schema.__belongs_to__/4
    web/models/waiver.ex:7: (module)
    (stdlib) erl_eval.erl:670: :erl_eval.do_apply/6

If I change :add and :drop to :fantasy_player for the belongs_to name I get:

== Compilation error on file web/models/waiver.ex ==
** (ArgumentError) field/association :fantasy_player is already set on schema
    lib/ecto/schema.ex:1355: Ecto.Schema.put_struct_field/3
    lib/ecto/schema.ex:1335: Ecto.Schema.association/5
    lib/ecto/schema.ex:1189: Ecto.Schema.__belongs_to__/4
    web/models/waiver.ex:8: (module)
    (stdlib) erl_eval.erl:670: :erl_eval.do_apply/6

What’s the proper way to set this up? Thanks!

22 comments

/phoenix #ecto

7 14048 22

2022-10-07 18:38:34 UTC

First 10 of 22 Posts

hubertlepicki

I think your column names are incorrect. Instead of add use add_id. Instead of drop, use drop_id. Use _id versions everywhere where you reference columns, and add/drop for associations. For example:

belongs_to :add, Ex338.FantasyPlayer, foreign_key: :add_id
belongs_to :drop, Ex338.FantasyPlayer, foreign_key: :drop_id

Post #1

axelclark

Thanks! I went back and renamed my columns in the migration to fantasy_team_id, add_fantasy_player_id, and drop_fantasy_player_id. I think those will work better with the default options and are much clearer.

Post #2

brayhoward

Hey @axelclark, have you figured this out yet? I’m doing a similar type thing where I have an Admin class and a CaseStudy class. I would like my case_studies to belong to admins through two different columns, [:created_by_id, :updated_by_id].

I keep getting and error though. ** (ArgumentError) field/association :case_studies is already set on schema

Here’s some codes.

defmodule MyApp.Admin do
  field :name, :string

  has_many :case_studies, CaseStudy, foreign_key: :created_by_id
  has_many :case_studies, CaseStudy, foreign_key: :updated_by_id
end

defmodule MyApp.CaseStudy do
  field :title, :string

  belongs_to :admin, MyApp.Admin, foriegn_key: :created_by_id
  belongs_to :admin, MyApp.Admin, foriegn_key: :updated_by_id
end

Post #3

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

You have to name them different things:

  has_many :created_case_studies, CaseStudy, foreign_key: :created_by_id
  has_many :updated_case_studies, CaseStudy, foreign_key: :updated_by_id

Post #4

brayhoward

Ahh, that seems so obvious now. Thank you very much for the reply @benwilson512.

Post #5

demem123

Hi,

Just wondering, how you assoc the add and drop in query? can you do it on the same line?

thanks!

Post #6

axelclark

I’m not sure exactly what you mean by “on the same line”, but you treat them as two different associations. For example here are some samples:

defmodule Ex338.Waiver do
  schema "waivers" do
    belongs_to :add_fantasy_player, Ex338.FantasyPlayer
    belongs_to :drop_fantasy_player, Ex338.FantasyPlayer
  end
  
  def query(query) do
    from w in query,
      inner_join: a in assoc(w, :add_fantasy_player),
      inner_join: d in assoc(w, :drop_fantasy_player)
  end

  def preload_assocs(query) do
    from w in query,
      preload: [:add_fantasy_player, :drop_fantasy_player]
  end
end

defmodule Ex338.FantasyPlayer do
  schema "fantasy_players" do
    has_many :waiver_adds, Ex338.Waiver, foreign_key: :add_fantasy_player_id
    has_many :waivers_drops, Ex338.Waiver, foreign_key: :drop_fantasy_player_id
  end

  def waiver_adds(query) do
    from f in query,
      inner_join: w in assoc(f, :waiver_adds),
      preload: [:waiver_adds]
  end
end

Post #7

demem123

thanks! I just started doing phoenix and echo is confusing! I will take a look at your code!

Post #8

demem123

Hi,

I just managed to get mine to work. But im seeing a weird behavior. If I use a limit: size to the query, the results are wrong. Have you tried to use the limit?

Thanks!

Post #9