How to use nested preload with multiple repos in Ecto

I have a usecase where I want to use nested preload data from multiple repos.
For eg:

I have
posts -> comments – posts database
comments have user id linked to it in schema.

users – users database

I want to load all posts with comments and its users.
similar to this ->
data = from post in Blog.Post,
preload: [comments: :user]

But user is in different database.

You can’t.

a nice request :smile: I’ve created a request for this in Google group. If it’s accepted, I’il do it.

Not even sure a DB engine would allow you to join records between two different databases. There are workarounds (for Postgres anyway):

Joining Results from Two Separate Databases


Don’t think of a cross-db transaction. This type of operations can be made configurable. For ex: Ecto.Multi.query(). When creating the query, it can be created by taking the necessary parameters from the multi-repos settings.

I think this is possible e.g. with something like Dblink in postgres. However to do this in Ecto we will have to use two different connections and Two different Repos, which is not supported as of now. However there could be multiple workarounds but something like this without any SQL extentions/plugins is not possible to my knowledge.