How to make a join query between multiple schemas with ecto

Questions / Help
1

Hi,

Here my problem, I try to make a query that join one table from a public schema and one table for another schema.
In our system user have roles and we have the following tables

User -> UserRolesMapping > Role

*UserRolesMapping is in a schema (one per customer for exemple) and role is in the public schema (all roles are the same for all customers)

What I try to get is role for a user

from roles in "roles",
  join: user_role in ^"tenant_#{organization_id}.users_roles_mappings" , on: roles.id == user_role.role_id,
  where: user_role.user_id == ^user.id

This generate a query like that :

SELECT r0."id", r0."name", r0."description", r0."application_id", r0."default", r0."inserted_at", r0."updated_at" FROM "roles" AS r0 INNER JOIN "tenant_11.users_roles_mappings" AS t1 ON r0."id" = t1."role_id" WHERE (t1."user_id" = 1)

Which seem invalid in postgres

but this one work

SELECT r0."id", r0."name", r0."description", r0."application_id", r0."default", r0."inserted_at", r0."updated_at" FROM "roles" AS r0 INNER JOIN tenant_11.users_roles_mappings AS t1 ON r0."id" = t1."role_id" WHERE (t1."user_id" = 1)

How can i make this work ? Maybe there is better way of doing this kind of stuff ?

2

It is not possible to do cross schema queries using the query syntax. The query you have defined is unsafe depending from where organization_id is coming, that’s why we escape it.