How do you model different ‘types’ with totally different fields in Ecto?

I’m slightly confused by your wording… items_a and items_b, the plural makes me think it’s a has_many, but you then you said hash_one so I’m assuming that. SO, I actually have a mostly working on a library to handle this more gracefully as we had a few tables like that at my old work. But since I stopped working there I lost motivation. I could post what I have, though. The API is basically:

schema "items" do
  field :foo, :string

  one :item do
    belongs_to :item_one, Item_1
    belongs_to :item_two, Item_2
    belongs_to :item_three, Item_3
  end

  def changeset(item, attrs) do
    item
    |> cast(attrs, [:foo])
    |> cast_one_assoc(:item)
  end
end

It basically adds sister versions of a few functions with _one added (kinda like _embeds versions), so cast_one, put_one_)assoc, and get_one_assoc (for each you can refer to them by the first argument to the one macro). There is also MyRepo.preload_one (which is a stupid name and I think that function is unfinished for nested preloads). I can post what I have if you’re interested.

Otherwise, as far as preloading goes, you can do this and it’s efficient:

Repo.preload(items, [:item_a, :item_b, :item_c)

When preloading it will simply ignore the associations whose ids are nil. I would definitely hide this in a function, though, as they get unweildly if you are preloading a lot.

Yeah, good point about the naming — that’s on me. it’s has_one in practice.

Your one :item abstraction is actually very close to what I’m doing manually.

I’d definitely be interested in seeing it, especially how you handled:

• dispatching based on type
• and preloading (that part gets messy fast)

Also curious: did you end up using this pattern long-term, or did you move away from it?

I’m definitely interested, mostly from a learning perspective.

This approach feels “right” to me so far — especially compared to pushing everything into a JSON field. Even if it means a few extra tables and joins, I like that it keeps things well-structured and avoids nullable fields.

I should also mention that I haven’t really worked with macros yet, so seeing how you approached that part would be especially interesting.

On the UI side, I’ve also tried modeling this in LiveView forms using function components for each subtype, so each type has its own set of inputs. It works, but after a while it starts to feel a bit cumbersome to maintain.

Sure thing! I’ll clean it up and push it a bit later and let you know.

Note I made a mistake in the API I showed which I’ve edited. I just forgot the belongs_tos so the one macro is doesn’t do anything too magical. It’s has a lot in common with how test works which is outlined in the official guides.

And yes, preloading gets hairy if they are deeply nested, but it’s just a wrapper around Repo.preload that looks for :item and replaces it with the proper preload. Of course, it’s not that simple because it actually has to reflect on the schema to ensure that :item in a preload really does mean the one :item since it could refer to something else in a nested preload. That’s where it gets messy.

I’m sure my old team has not moved from this pattern (I only left a few weeks ago, lol) and ya, I don’t think there is anything wrong with it. Ecto very specifically does not support polymorphic associations with a type column.

On speaking of that, my lib allows for adding either a virtual or concrete type column which can come in handy in pattern matching and doing all queries for a specific type respectively.

I’ll let you know when I’ve posted it.

I recently read a good blog post on this:

This is about the plain db level. Once you have that you can add the schemas for that.

You likely want to have another layer of this eventually, which can map between [ItemA, ItemB] to those lower level nested schemas.

I really like this, thanks for sharing. It’s quite clever and a pretty clean solution.

As promised here is the repo. I’m a bit embarrassed by the preload_one as I was in the middle of working on it (this was made with 0 vibes) but I keep hopping around on projects and I need to stop doing that so here ya go!

I think the approach linked by LK is a much more interesting way of doing this, although the same Ecto API could still apply.

Also the README is not fleshed out at all but the all the docstrings are.

This is a non-answer but something I learned to do early on in a project is not even worry about it and just have all the columns on the one table and then document the schema which fields belong to which subtype and then have separate changesets for creating the different subtypes.

This is usually good enough until a better understanding of requirements show themselves.

This is the strategy my library was trying to solve. We did just this at my last job but it ballooned very quickly. EDIT: By “solve” I mean make easier, but same idea: it’s when you put all the sub types right on the parent table and only allow one of them to be present.

Whats easy in SQL are hard in Ecto.

Thats why I’m building GitHub - elixir-dbvisor/sql: SQL provides state-of-the-art, high-performance SQL integration for Elixir, built to handle extreme concurrency with unmatched expressiveness and ergonomic query composition. Write safe, composable, parameterized queries directly, without translating to Ecto or any ORM. · GitHub to make it easy both places.

Oh ya, I keep meaning to check this out. It looks really nice I just haven’t played with it yet as there are a bunch of things I wanna do (and someone recently starting to use AI a bit has made that worse). I’ve also been heavy into an Ash project and I’m ass-u-me’ing it wouldn’t make much sense to use in Ash?