Multiple associations between two models

Questions / Help
,
1

I’m trying to model a simple situation where there are two different many_to_many associations between two models Book and Person.

  1. Person is an author of a Book
  2. Person is mentioned in a Book

I would like to use two intermediary tables authors_books and mentioned_in_books with foreign keys person_id and book_id but cannot figure out how to define two different many_to_many associations pointing to the same model in one schema.

2

Check part about many_to_many with join_through parameter

defmodule Post do
  use Ecto.Schema

  schema "posts" do
    many_to_many :comments, Comment, join_through: "posts_comments"
  end
end

defmodule Task do
  use Ecto.Schema

  schema "tasks" do
    many_to_many :comments, Comment, join_through: "tasks_comments"
  end
end
1 Like
3

Yes, that’s what I have figured out already. The point is how I could have something like

 defmodule Post do
   use Ecto.Schema

   schema "posts" do
     many_to_many :comments, Comment, join_through: "posts_favourite_comments"
     many_to_many :comments, Comment, join_through: "posts_clever_comments"
   end
 end

 defmodule Task do
   use Ecto.Schema

   schema "tasks" do
     many_to_many :comments, Comment, join_through: "tasks_favourite_comments"
     many_to_many :comments, Comment, join_through: "tasks_clever_comments"
   end
end
1 Like
4

This would work and all comments would be in the in in comments table

5

Great! So I did it myself :wink: But how should I query all favourite comments only? Can I use posts_favourite_comments in a query?

6

You can use any table in query, however if you want all comments only, then I would use this
Repo.all(Comment)

1 Like
7

Would it be possible to define schemas FavouriteComment and CleverComment so I could do e.g. Repo.all(FavouriteComment) ?

8

Sure, however then you should map FavouriteComment and CleverComment modules to get data from all four tables and trough those get real comments. Because those tables only contains comment_id.

1 Like
9

Great, many thanks! Seems like a nice exercise. Will give it a try.

1 Like