How to tell Ecto that the `belongs_to` association is tied to a table in another schema

Questions / Help
1

I have the following field defined in a tenant migration (i.e. the migration is run with a schema prefix):

create table(:tasks) do
  ...
  add :job_id, references("oban_jobs", prefix: "public")
  ...
end

How does this translate to the schema when defining a belongs_to association?

I have this:

schema "tasks" do
   ...
   belongs_to :job, Oban.Job
   ...
end

But that results in an exception when preloading, as Ecto looks for the job in the schema that is defined using the prefix option:

query =
    from is in Task,
    preload: [:job]
  Repo.get!(query, id, prefix: tenant_prefix)

Is there a way to tell Ecto that the foreign key refers to another schema, and the association should be fetched from that schema?

2

In the schema module for the association, you can use the @schema_prefix attribute

https://hexdocs.pm/ecto/Ecto.Schema.html#module-schema-attributes

This will be picked up when you’re doing queries.

Although I see you’re using Oban so you might not be able to define your own schema modules. I’m not too sure how that library works.

1 Like
3

I thought it was a limitation of Ecto where the schema prefix will be the same for everything in the same query, so you can’t get assocs from another prefix. In general queries across pg schemas did/do not work well in Ecto 3. I worked around the problem and changed the design of my system. This was 2 years ago.

Would urge consideration if you could do the same. If not, and if you are desperate, you could make a VIEW that selects from the schema

i.e.

CREATE TABLE other_schema.jobs 
CREATE VIEW current_schema.jobs AS SELECT * from other_schema.jobs
CREATE TABLE current_schema.tasks

It is dirty but for the purpose of Ecto preloads it will probably work.

4

The control Ecto affords for prefix is pretty granular…for sure you don’t need to have everything in the same query under the same prefix:

Ecto Prefix Guide

1 Like
5

Should do the trick