Is it possible to define "virtual schema" (something like a DB View)

Hi everybody,

I don’t know how to explain it without being confusing so let me give an example right away.
Let’s say I have two schemas (User and Profile) that somehow match with their related DB tables:

defmodule MyApp.User do
  schema "users" do
    field :email, :string
    field :password, :string
    field :name, :string
  end
end

defmodule MyApp.UserProfile do
  schema "user_profiles" do
    field :phone, :string
    field :avatar, :string
    belongs_to :user, MyApp.User
  end
end

Now I can access to a user’s phone by grabbing that user, loading its profile then accessing like so user.profile.phone.

Now if I have very different kind of users (with very different profiles and behavior), it might become complex real quick… I think of a legacy application in Rails that deal with all kind of Design Patterns like polymorphic association or Single Table Inheritance, etc.

I love working with contexts and I guess that reasoning with context could potentially simplify this hugely.
For example I found the possibility to use different schemas for a given same table (an example posted in the forum) according to a particular context very useful.

There we are using different schema from the same table, but I wonder if it’s possible to do the exact opposite like having a schema defined from multiple tables?

For example, having something like that as an end result (considering a different PartnerUser with a complete different set of profile on its own table) :

defmodule MyApp.Dashboard.RegularUser do
  some_kind_of_schema do
    field :name, :string, from: "users"
    field :avatar, :string, from: "user_profiles"
    # other fields like password or phone ignored
  end
end

defmodule MyApp.Dashboard.Partner do
  some_kind_of_schema do
    field :email, :string, from: "users"
    field :name, :string, from: "users"
    field :department, :string, from: "partner_profiles"
    # other fields ignored
  end
end

So this is just an example of the end result and I put aside how this can be implemented with joins etc…

Also I am pretty sure that I’m currently over-engineering this and that it might be something very simple (like when using PORO in Rails).

I’m just stick and focused on a task right now that I can’t see the wood for the trees… and that I can’t event think of a solution using bare elixir aside from exposing a dedicated struct that I can flatten from a public function…

Maybe this is simply the solution?

I’m very welcome to any suggestion.

No, Ecto does not allow that.

That‘s not really true. „Abstract schemas“ do that:

https://medium.com/@lostkobrakai/phoenix-contexts-and-ecto-abstract-tables-e6b4de93edf

Oops, I stand corrected. I even used them once. :smile:

2 Likes