Implementing Class Table Inheritance in Ecto

I would like to use Class Table Inheritance (CTI). In standard SQL (without the assistance of Ecto), I would create the base class as its own table, then create a separate table for each derived class with its independent properties as additional columns.

I tried doing this with Ecto but it seems that if you use “belongs_to” you must also use “has_one” on the other side of the association. The “belongs_to” side of things was easy to resolve, since the base class is the same type (schema?) in all cases, but on the other side, the “has_one” can reference more than one type.

How can I resolve this?

No problem at all, I’m doing the same in one of my apps (I’ve got an account table with an id, and then a user and group table whose id is a foreign key on the account table. I simply omit the belongs_to field, because I either load a user or group because I need the additional data, or just an account if I need only the base data.

Does that help?

This seems like it would work:

  • BaseSchema that points to the common table

  • DerivedSchema1 and DerivedSchema2 point to specific tables for the derived cases

  • DerivedSchema1 and DerivedSchema2 both belong_to :base

  • BaseSchema has_one :derived_schema1 and has_one :derived_schema2

Again, big thanks for the response!

If I understood you correctly, you’re not using the belongs_to-has_one association and you are somehow manually building that data with Ecto? I am still very new to using Phoenix/Ecto and not sure how you can do that in Ecto and preserve the underlying association.

First off, big thanks for the response.

This looks like it should work. It will be a bit cluttered, especially in the event that the number of derived classes grows, but so far, this has been the best Ecto-esque solution I’ve seen, and the additional has_one clauses will be a small price to pay for the bang Ecto provides.

I was initially hoping I could do something like has_one Type A | Type B | Type C, but your solution is pretty much the valid application of that. I will give it a try, thanks again!

You might also consider using embeds (jsonb) and have a look at polymorphic_embed for working with dynamic data.

Not quiet, I am using the belongs_to part, but not the has_one part. I.e. the account does not know about any derived tables (just like an abstract base class does not know anything about derived classes). But I had another look into my source, I actually declared the belongs_to like this:

    belongs_to(
      :account,
      Account,
      foreign_key: :id,
      primary_key: true,
      type: :binary_id,
      define_field: false
    )

Afterwards I can get a User like this and preload the account:

Repo.get(User, id: xxxx) |> Repo.preload(:account)

Maybe that helps clarifying it.

My belongs_to took a look at your belongs_to and now it suffers from an identity crisis.

I could have sworn I got an error when I used belongs_to on one side but not a has_one on the other, I am guessing the part with define_field: false takes care of that? I am also guessing that a query in the reverse order, such as:

Repo.get(Account, id: xxxx) |> Repo.preload(:user)

no longer works since just like an abstract base class, it has no knowledge of the derived classes?

That definitely takes care of the cluttered base-class part, and certainly did clarify things. Thanks again!

Exactly, I simply never run the queries in this order:

Repo.get(Account, id: xxxx) |> Repo.preload(:user)