Preloading associations across databases

Phoenix Forum Questions / Help
, ,
1

I have a project with two repos: the main one is the core CRUD repo of the app (let’s just call it MainRepo) and the other is a read-only data store repo (call it SecondRepo).

In MainRepo, there is a schema, let’s call it Posts, and it belongs to a User (also in MainRepo), has some other attrs, and also belongs to an Item in the SecondRepo: this “Posts” table is thus like a virtual join that allows me to keep track of the relationship between records in both db’s.

Since I have both of them, however, I’d like to make the item_id field in Posts actually be a hard reference to the corresponding parent Item in SecondRepo. But it doesn’t seem like I can do this. Attempts to preload item records lead to:
(Postgrex.Error) ERROR 42P01 (undefined_table): relation “items” does not exist

I’m assuming this is because item_id is not a foreign key and was not migrated as a reference. Can I migrate a reference that refers to another database? If not, is there any other way around this?

I can obviously still select by this virtual join, so it’s not the end of the world, but obviously it’d be nice to be able to leverage preloading if I can.

1 Like
2

You should have a items schema similsr to the posts schema with a has many relationship with posts.

Ecto needs the items schema to map the table items.

3

I do have an “items” schema with the matching has_many relation for posts.

4

Are you starting your second repo when your application starts? Are you able to load data using My_app.SecondRepo.get(Item, id)?

5

Yeah I can load stuff no problem

6

So remember you are calling two different Repos.

I configured a small project, tried the following and worked:

My application is called:

Repos

I have two databases:

  1. Repo = database1
  2. Repo2 = database2

Schemas:

schema "users" do
  field(:name, :string),
  field(:email, :string),
  has_many :invoices, Repos.Invoice
end

schema "invoices" do
  field(:provider, :string),
  belongs_to :user, Repos.User
end

From iex -S mix:

alias Repos.{Invoice, User, Repo, Repo2}
Repo.all(Invoice) |> Repo2.preload(:user)
Repo2.all(User) |> Repo.preload(:invoices)

Please note that I am using both Repos.

Hope this helps.

Best regards,

3 Likes
7

Thanks! As usual, I should have looked a little bit closer at all the methods I was using.

The key was making sure to call Repo2.preload rather than throwing around naked preload within options where the wrong Repo was implicit. Thanks again!

2 Likes