Hello!
I have 2 applications in umbrella:
- Security (Postgres schema “security”)
- Health (Postgres schema “health”)
In the 1st application I have a model Security.Auth.User:
defmodule Security.Auth.User do
use Ecto.Schema
@schema_prefix "security"
schema "users" do
field :username, :string
field :password, :string, virtual: true
field :password_confirmation, :string, virtual: true
field :password_hash, :string
timestamps
end
In the 2nd application I have a model Health.Patient:
defmodule Health.Patient do
use Health.Web, :model
@schema_prefix "health"
schema "patients" do
field :surname, :string
field :name, :string
field :patronymic_name, :string
field :birth_date, Ecto.Date
belongs_to :user, Security.Auth.User
timestamps()
end
Also, I have FK from health.patients (patient_id) to security.users (id):
defmodule Health.Repo.Migrations.AddColumnToPatient do
use Ecto.Migration
@schema_prefix "health"
@security_schema_prefix "security"
def up do
execute "ALTER TABLE #{@schema_prefix}.patients
ADD CONSTRAINT patients_user_id_fkey
FOREIGN KEY (user_id) REFERENCES #{@security_schema_prefix}.users (id)
MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION"
end
def down do
execute "ALTER TABLE #{@schema_prefix}.patients
DROP CONSTRAINT patients_user_id_fkey"
end
end
Now I am trying to join this models:
import Ecto.Query
query = from p in Health.Patient,
join: u in Security.Auth.User,
on: p.user_id == u.id
Repo.all query
iex(5)> query = from p in Health.Patient,
...(5)> join: u in Security.Auth.User,
...(5)> on: p.user_id == u.id
#Ecto.Query<from p in Health.Patient, join: u in Security.Auth.User,
on: p.user_id == u.id>
And getting error:
iex(6)> Repo.all query
[debug] QUERY ERROR source="patients" db=13.1ms
SELECT p0."id", p0."surname", p0."name", p0."patronymic_name", p0."birth_date", p0."user_id", p0."inserted_at", p0."updated_at" FROM "health"."patients" AS p0 INNER JOIN "health"."users" AS u1 ON p0."user_id" = u1."id" []
** (Postgrex.Error) ERROR (undefined_table): relation "health.users" does not exist
(ecto) lib/ecto/adapters/sql.ex:429: Ecto.Adapters.SQL.execute_and_cache/7
(ecto) lib/ecto/repo/queryable.ex:127: Ecto.Repo.Queryable.execute/5
(ecto) lib/ecto/repo/queryable.ex:40: Ecto.Repo.Queryable.all/4
Seems that Ecto does not take @schema_prefix from model to build SQL query.
Trying to use schema.table in query:
query = from p in "health.patients",
join: u in "security.users",
on: p.user_id == u.id,
select: p.id
But it does not work, too:
iex(8)> Repo.all query
** (Postgrex.Error) ERROR (undefined_table): relation "health.patients" does not exist
[debug] QUERY ERROR source="health.patients" db=3.0ms
SELECT h0."id" FROM "health.patients" AS h0 INNER JOIN "security.users" AS s1 ON h0."user_id" = s1."id" []
(ecto) lib/ecto/adapters/sql.ex:429: Ecto.Adapters.SQL.execute_and_cache/7
(ecto) lib/ecto/repo/queryable.ex:127: Ecto.Repo.Queryable.execute/5
(ecto) lib/ecto/repo/queryable.ex:40: Ecto.Repo.Queryable.all/4
Because now Postgres thinks that “health.patients” is a table name, but not schema_name.table_name (health.patients).
Preloading data is working if prefix was specified:
Patient |> Repo.get(1) |> Repo.preload(:user, prefix: :security)
Am I doing something wrong?
Why doesn’t Ecto use @schema_prefix from model?
Are fragments the only possible way to do such queries?
I appreciate any help on this questions.
