Many to many using two Repo

I have two databases in different domains. For this, I created two Repo. The first database contains only the users table, this database is used by all our applications.

In my Phoenix application I have the following relationship, for example:

user schema

many_to_many :posts, App.Post, join_through: App.PostUser

post schema

many_to_many :users, App.User, join_through: App.PostUser

post_user schema

schema "posts_users" do
  belongs_to :post, App.Post
  belongs_to :user, App.User

How can I assign users to posts using different Repo?

I tried to do the following function, but some erros ocurr.

 def add_users(post_ids, user_ids \\ []) do
   post= Repo.get!(Post, post_id |> AnotherRepo.preload(:users)
   users = AnotherRepo.all(from u in User, where: in ^user_ids)

   |> Post.changeset(%{})
   |> put_assoc(:users, users)
   |> Repo.update()
** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "posts" does not exist

query: SELECT u0."id", u0."name", u0."email", r1."id" FROM "users" AS u0 INNER JOIN "posts" AS r1 ON
r1."id" = ANY($1) INNER JOIN "posts_users" AS r2 ON r2."post_id" = r1."id" WHERE (r2."user_id" = u0."id") ORDER BY r1."id"

1 Like

Each repo maintains it’s own connections and you cannot query across connection boundries. If things are still in the same database you might be able to use a single repo. If not such a relationship is not possible at all.


Though if you are using postgresql and add in the appropriate FDW’s for the other databases then postgresql can do the many-to-many mappings for you. ^.^