Add two foreign key columns that reference the same table

axelclark · August 3, 2016, 1:44am

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!

hubertlepicki · August 3, 2016, 8:21am

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

axelclark · August 3, 2016, 11:02am

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.

brayhoward · October 5, 2016, 3:26pm

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

benwilson512 · October 5, 2016, 4:17pm

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

brayhoward · October 5, 2016, 4:45pm

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

demem123 · May 2, 2017, 4:02pm

Hi,

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

thanks!

axelclark · May 2, 2017, 10:42pm

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

demem123 · May 3, 2017, 1:04am

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

demem123 · May 4, 2017, 9:47pm

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!

axelclark · May 5, 2017, 1:45am

What does your query look like? Are you getting more or less than expected?

demem123 · May 9, 2017, 10:25pm

Hi!

I’m still stuck. Thanks for the help! I’m still very new to this.

I have to work with:

2 tables like this below:

create table(:posts_tags, primary_key: false) do
add :post_id, references(:posts)
add :tag_id, references(:tags)
end

create table(:posts) do
add :title
add :body
timestamps()
end

create table(:tags) do
add :name
timestamps()
end

in my post model in phoenix, i have 2 many_to_many tags since I have 2 tables i want to preload with
many_to_many :tags_a, MyApp.Tag, join_through: “posts_tags”
many_to_many :tags_b, MyApp.Tag, join_through: “posts_tags”
I want to do a single join to preload the struct but its not working

from q in query,
left_join: pa in assoc(q, :tags_a),
left_join: pb in assoc(q, :tags_b),
preload: [prints_a: pa, prints_b: pb]

test_a = from t in query,
join: pa in assoc(t, :tags_a),
where: t.id == pa.tag_id

test_b = from t in query,
join: pb in assoc(t, :tags_b),
where: t.id == pb.tag_id

from q in query,
preload: [pa: ^test_a, pb: ^test_b]

axelclark · May 12, 2017, 1:36am

If you are trying to associate many tags (i.e. tag_a and tag_b) with many (or a single) post(s) then you don’t need two many to many lines in your post Schema like you have. You could delete one and rename it to just many_to_many :tags.

Alternatively, you could delete your join table and add a tag_a_id column and tab_b_id column to your posts table, then use two has many lines and belongs_to lines in your schemas. However unless there is some reason you only want two tags, your join table is probably a better solution.

Can you give an example of what you expect your query to return given certain rows in your database?

axelclark · May 12, 2017, 3:07am

Based on your tables, I would have this schema:

defmodule MyQuery.Post do
  use MyQuery.Web, :model

  schema "posts" do
    field :title, :string
    field :body, :string
    many_to_many :tags, MyQuery.Tag, join_through: "posts_tags"

    timestamps()
  end
end

defmodule MyQuery.PostTag do
  use MyQuery.Web, :model

  schema "posts_tags" do
    belongs_to :post, MyQuery.Post
    belongs_to :tag, MyQuery.Tag

    timestamps()
  end
end

defmodule MyQuery.Tag do
  use MyQuery.Web, :model

  schema "tags" do
    field :name, :string
    many_to_many :posts, MyQuery.Post, join_through: "posts_tags"

    timestamps()
  end
end

With 2 posts, 3 tags, and post1 associated with tags 1 & 2 and post 2 associated with tags 2 & 3:

iex(15)> Repo.all(Post)
[debug] QUERY OK source="posts" db=1.2ms
SELECT p0."id", p0."title", p0."body", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 []
[%MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
  body: "Body Text", id: 1, inserted_at: ~N[2017-05-12 02:45:40.606380],
  tags: #Ecto.Association.NotLoaded<association :tags is not loaded>,
  title: "Title1", updated_at: ~N[2017-05-12 02:45:40.606387]},
 %MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
  body: "Body Text", id: 2, inserted_at: ~N[2017-05-12 02:46:01.298796],
  tags: #Ecto.Association.NotLoaded<association :tags is not loaded>,
  title: "Title2", updated_at: ~N[2017-05-12 02:46:01.298802]}]

iex(16)> Repo.all(Tag)
[debug] QUERY OK source="tags" db=0.8ms
SELECT t0."id", t0."name", t0."inserted_at", t0."updated_at" FROM "tags" AS t0 []
[%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 1,
  inserted_at: ~N[2017-05-12 02:44:03.558857], name: "Sports",
  posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
  updated_at: ~N[2017-05-12 02:44:03.567081]},
 %MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 2,
  inserted_at: ~N[2017-05-12 02:44:03.588083], name: "News",
  posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
  updated_at: ~N[2017-05-12 02:44:03.588089]},
 %MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 3,
  inserted_at: ~N[2017-05-12 02:44:03.590854], name: "Opinion",
  posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
  updated_at: ~N[2017-05-12 02:44:03.590859]}]

iex(17)> Repo.all(PostTag)
[debug] QUERY OK source="posts_tags" db=0.9ms
SELECT p0."id", p0."post_id", p0."tag_id", p0."inserted_at", p0."updated_at" FROM "posts_tags" AS p0 []
[%MyQuery.PostTag{__meta__: #Ecto.Schema.Metadata<:loaded, "posts_tags">, id: 1,
  inserted_at: ~N[2017-05-12 02:47:30.915457],
  post: #Ecto.Association.NotLoaded<association :post is not loaded>,
  post_id: 1, tag: #Ecto.Association.NotLoaded<association :tag is not loaded>,
  tag_id: 1, updated_at: ~N[2017-05-12 02:47:30.915462]},
 %MyQuery.PostTag{__meta__: #Ecto.Schema.Metadata<:loaded, "posts_tags">, id: 2,
  inserted_at: ~N[2017-05-12 02:48:01.578237],
  post: #Ecto.Association.NotLoaded<association :post is not loaded>,
  post_id: 1, tag: #Ecto.Association.NotLoaded<association :tag is not loaded>,
  tag_id: 2, updated_at: ~N[2017-05-12 02:48:01.578242]},
 %MyQuery.PostTag{__meta__: #Ecto.Schema.Metadata<:loaded, "posts_tags">, id: 3,
  inserted_at: ~N[2017-05-12 02:48:20.769033],
  post: #Ecto.Association.NotLoaded<association :post is not loaded>,
  post_id: 2, tag: #Ecto.Association.NotLoaded<association :tag is not loaded>,
  tag_id: 2, updated_at: ~N[2017-05-12 02:48:20.769039]},
 %MyQuery.PostTag{__meta__: #Ecto.Schema.Metadata<:loaded, "posts_tags">, id: 4,
  inserted_at: ~N[2017-05-12 02:48:39.862811],
  post: #Ecto.Association.NotLoaded<association :post is not loaded>,
  post_id: 2, tag: #Ecto.Association.NotLoaded<association :tag is not loaded>,
  tag_id: 3, updated_at: ~N[2017-05-12 02:48:39.862816]}]

Here is how I would preload the tags on post1 (you can see the two tags loaded under the tags key):

iex(19)> query = from p in Post, where: p.id == 1, preload: [:tags] 
#Ecto.Query<from p in MyQuery.Post, where: p.id == 1, preload: [:tags]>

iex(20)> post1 = Repo.all(query)                                    
[debug] QUERY OK source="posts" db=1.3ms
SELECT p0."id", p0."title", p0."body", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 WHERE (p0."id" = 1) []
[debug] QUERY OK source="tags" db=2.7ms
SELECT t0."id", t0."name", t0."inserted_at", t0."updated_at", p1."id" FROM "tags" AS t0 INNER JOIN "posts" AS p1 ON p1."id" = ANY($1) INNER JOIN "posts_tags" AS p2 ON p2."post_id" = p1."id" WHERE (p2."tag_id" = t0."id") ORDER BY p1."id" [[1]]
[%MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
  body: "Body Text", id: 1, inserted_at: ~N[2017-05-12 02:45:40.606380],
  tags: [%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 1,
    inserted_at: ~N[2017-05-12 02:44:03.558857], name: "Sports",
    posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
    updated_at: ~N[2017-05-12 02:44:03.567081]},
   %MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 2,
    inserted_at: ~N[2017-05-12 02:44:03.588083], name: "News",
    posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
    updated_at: ~N[2017-05-12 02:44:03.588089]}], title: "Title1",
  updated_at: ~N[2017-05-12 02:45:40.606387]}]

It looks like you are trying to join and then preload, but your post row is going to be duplicated in the join to match the two tag rows and the tags will be preloaded on the duplicate posts. This may be why you are getting an unexpected result:

iex(21)> query2 = from p in Post, join: t in assoc(p, :tags), where: p.id == 1, preload: [:tags]
#Ecto.Query<from p in MyQuery.Post, join: t in assoc(p, :tags), where: p.id == 1, preload: [:tags]>

iex(22)> post2 = Repo.all(query2)                                               
[debug] QUERY OK source="posts" db=3.5ms
SELECT p0."id", p0."title", p0."body", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 INNER JOIN "posts_tags" AS p2 ON p2."post_id" = p0."id" INNER JOIN "tags" AS t1 ON p2."tag_id" = t1."id" WHERE (p0."id" = 1) []
[debug] QUERY OK source="tags" db=2.6ms
SELECT t0."id", t0."name", t0."inserted_at", t0."updated_at", p1."id" FROM "tags" AS t0 INNER JOIN "posts" AS p1 ON p1."id" = ANY($1) INNER JOIN "posts_tags" AS p2 ON p2."post_id" = p1."id" WHERE (p2."tag_id" = t0."id") ORDER BY p1."id" [[1]]
[%MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
  body: "Body Text", id: 1, inserted_at: ~N[2017-05-12 02:45:40.606380],
  tags: [%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 1,
    inserted_at: ~N[2017-05-12 02:44:03.558857], name: "Sports",
    posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
    updated_at: ~N[2017-05-12 02:44:03.567081]},
   %MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 2,
    inserted_at: ~N[2017-05-12 02:44:03.588083], name: "News",
    posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
    updated_at: ~N[2017-05-12 02:44:03.588089]}], title: "Title1",
  updated_at: ~N[2017-05-12 02:45:40.606387]},
 %MyQuery.Post{__meta__: #Ecto.Schema.Metadata<:loaded, "posts">,
  body: "Body Text", id: 1, inserted_at: ~N[2017-05-12 02:45:40.606380],
  tags: [%MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 1,
    inserted_at: ~N[2017-05-12 02:44:03.558857], name: "Sports",
    posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
    updated_at: ~N[2017-05-12 02:44:03.567081]},
   %MyQuery.Tag{__meta__: #Ecto.Schema.Metadata<:loaded, "tags">, id: 2,
    inserted_at: ~N[2017-05-12 02:44:03.588083], name: "News",
    posts: #Ecto.Association.NotLoaded<association :posts is not loaded>,
    updated_at: ~N[2017-05-12 02:44:03.588089]}], title: "Title1",
  updated_at: ~N[2017-05-12 02:45:40.606387]}]

demem123 · May 12, 2017, 9:54am

Hey! thanks so much for the response! I really appreciate it! I have sleepless nights!

I cant modify the existing structure of the database. But i have 2 post_tags table because both tables might contain different information.

Example:
posts table has id = 1
one of the posts_tags table can have post_id = 1, tag_id = 100
the other posts_tags table can have post_id = 1, tag_id = 200 (or even none at all) so I used a left join.

In my model, I want to be able to load:
%Post{
tags_a: [{ tag_id: 100 info…}, ]
tags_b: []
}

preload by itself without the join works but i was wondering if I can convert it to a single query (trying to optimize unsuccessfully)

cause in the ecto docs i have seen
Repo.all from p in Post,
join: c in assoc(p, :comments),
where: c.published_at > p.updated_at,
preload: [comments: c]

so I thought I can do
Repo.all from p in Post, join:t in assoc(p, :tags_a), join: t2 in assoc(p, :tags_b), preload: [tags_a: t, tags_b: t2]

=(

axelclark · May 12, 2017, 10:17am

Do you have a :tags_a and a :tags_b key in your Post schema? With a join table, you’ll get both tags in a list under tags, not two keys, tags_a and tags_b.

axelclark · May 12, 2017, 10:22am

Also, preload does the left join for you under the hood.

axelclark · May 12, 2017, 10:31am

One last thing, there isn’t a concept of tag_a and tag_b in your posts table, just tags through your posts_tags join table.

Sorry for short answers, on phone, commuting.

axelclark · May 12, 2017, 11:07am

The join in this query is necessary to compare the published_at dates in the comments to the updated_at dates in the posts. The join is not necessary to preload the comments (preload does the left join on its own all with the same query).

The comments are preloaded for all posts that meet the

join: c in assoc(p, :comments),
where: c.published_at > p.updated_at

criteria.

What you should be getting back in your queries are:

# with one tag
%Post{
   tags: [{ tag_id: 100 info...}]
   }

# with no tags
%Post{
   tags: []
   }

# with two tags
%Post{
   tags: [{ tag_id: 100 info...}, { tag_id: 200 info...}]
   }

demem123 · May 12, 2017, 3:14pm

thanks!