Multi-tenancy and preloads not working as expected

[egeersoz]

Elixir version

1.16.0

Database and Version

14.2

Ecto Versions

3.10.3

Database Adapter and Versions (postgrex, myxql, etc)

Postgrex 0.17.0

Current behavior

This is related to https://github.com/elixir-ecto/ecto/issues/3387, but pertains to preloading associations where the associated record is on another schema. My use case is a multi-tenant app where each tenant has its own schema. Some tenant records have foreign keys that point to records in the public schema. The relevant tenant migration line looks like this:

add :agent_reminder_job_id, references("oban_jobs", on_delete: :nilify_all, prefix: "public")

The migration itself works fine, i.e. Ecto creates the foreign key association correctly. The problem rears its head when preloading records. For example:

Ecto schema:

schema "invoices" do
  ...
  belongs_to :agent_reminder_job, Oban.Job # this schema is under `public` 
end

query =
  from i in Invoice,
  preload: [:agent_reminder_job]

Repo.one(query, prefix: "my_tenant_schema")

This results in:

(Postgrex.Error) ERROR 42P01 (undefined_table) relation "my_tenant_schema.oban_jobs" does not exist

Expected behavior

Ecto should be"smart" enough to figure out that it should look at the public.oban_jobs table to preload the associated Oban.Job record.

The most user-friendly method would be adding a prefix option to schema association functions (e.g. belongs_to) that mirrors the prefix option in Ecto.migration.references/2.