Ecto change @schema_prefix on a join statement

[CastLouis]

hi

i am creating a query to the database, i am using ecto, and it change the @schema_prefix of my module ex:

defmodule NA.DB.Schema.Detail do
  use Ecto.Schema
  @schema_prefix "schema1"

  schema "table" do
    field :id, :integer
    field :master_id, :integer
   end
end

the another module:

defmodule NA.DB.Schema.Master do
  use Ecto.Schema
  
  @schema_prefix "schema2"
  @primary_key false
  
  schema "patient_eligibility_list" do
    field :item_id, :integer
    field :parent_id, :integer
    field :num, :integer
  end
end

the query:

from p in Patient,
      join: pel in Master,
      join: group in Detail,
      where: group.id == pel.group_id, 
      select: %{item_id: pel.item_id, parent_id: pel.parent_id, group_num: group.group_num}   

and it change the schema of the detail to the schema from the master and it throw a error that a table doesn’t exist.

[OvermindDL1]

Due to a conscious design decision by the Ecto team Ecto does not allow cross-schema joins for reasons of things that I’ve never seen in real-life at any of the corporations that I’ve worked for in the past 2 decades but I am completely sure is used ‘out in the wild’ (just I really do not think that the case is that common based on my life experience). So right now, “Not Possible with Ecto”.

For elaboration, it is designed for the purpose of being able to change the schema of an ‘entire’ query for separating, say, user websites by schema name. I can see that use case being useful, I’ve just never seen it done myself, and the fact that you can do @schema_name on a schema does not seem to fit that model at all so I do not know why @schema_name exists at all then (you’d think if you are not overriding the schema name on a query that the default @schema_name should be used instead yes? But it is not, it is completely and entirely ignored unless it is in the from). What I usually see in every business I’ve ever worked (mostly oracle DB’s) is that they separated schemas based on ‘purpose’, so all user related data would be in one schema, say for a school then all course data would be in another schema (foreign-linking back to the user schema) and so forth…

I tried to suggest once to default to the @schema_name if the schema was not otherwise specified, but it was very very quickly and loudly denied, thus completely making this use-case impossible with the ecto queries (I still do not understand why, if anyone wanted to unify all the schemas then they could leave @schema_name off and/or override the schema names in the repo function directly as you already can…).

[mbuhot]

What are the current best workarounds for this limitation in Ecto?

Define a sql view in a common schema that does the join?

Use Ecto schemaless queries?

[OvermindDL1]

Either of those two work. I don’t like the schemaless bit as it does not fit in my model of stuff well (and I’ve not tried it honestly, I just dumped down into raw SQL…), and view’s I try to minimize (unless I’m making a materialized view or so) in case I am forced into the Oracle database itself from PostgreSQL (where I’d not be allowed to create a view)… >.>

[CastLouis]

i was using another post to resolve it:

Ecto Cross-schema join

FWIW, you should be able to do: join: c in fragment("some_other_schema.example") or at least: join: c in fragment("SELECT * FROM some_other_schema.example") which you can easily encapsulate in a macro. But you do lose the Ecto schema information (although Ecto has many tools to help with that).

[OvermindDL1]

But you do lose the Ecto schema information (although Ecto has many tools to help with that).

Yep, that is the raw sql way, and yeah it loses the type information so you have to re-type everything manually, which sucks when there are 40+ columns. ^.^;