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.
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ā¦).
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)ā¦ >.>
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. ^.^;