PaleWatcher
How do you model different ‘types’ with totally different fields in Ecto?
I’m trying to model something in Ecto where records share a few common fields, but depending on a type, they have completely different additional fields.
Roughly:
- there’s an
itemstable with the common stuff (type,same_field, etc.) - and then separate tables for each type (
items_a,items_b) - these subtype tables use the same
idas the parent (so 1:1, PK = FK)
So everything is still one logical collection, just split across tables to avoid a lot of nullable columns and to keep proper DB constraints.
In Ecto I’m using has_one + cast_assoc, and dispatching based on the type.
I’m wondering:
- does this approach fit well with Ecto, or is it something people generally avoid?
- is there a more idiomatic way to solve this without collapsing everything into one table or using JSON?
- are there any gotchas with this setup (especially around inserts or preloading)?
Example changeset:
def changeset(item, attrs) do
item
|> cast(attrs, [:type, :category, :owner_id])
|> validate_required([:type, :category, :owner_id])
|> cast_subtype()
end
The whole idea is to keep validation at the database level as much as possible, not just in changesets.
First Post!
sodapopcan
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.
Most Liked
LostKobrakai
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.
krasenyp
Then there’s no time to refactor the database schema. I always encourage people to not half-ass the database design. Do it properly from the get go. It’s like in sports. If you lack the fundamentals, it’s very hard to compensate afterwards.
Schultzer
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.
Last Post!
GrammAcc
Your data model looks like a regular tagged union to me. Is there some reason you can’t use a separate table for each item type? If the relations are 1;1, then there’s not really any difference between the items;item_one schema and just an item_one schema with all the fields from both items and item_one on it. This also avoids a join, which can help if you need to sort across multiple columns since Postgres at least can’t use ordered indexes across joins.
Since you’ve got a tag field for the tagged union (the type field), you can pattern match on the value of that field passing structs or Ecto Schemas around in application code, and if each item type is a separate table, you could use Ecto Schemas and Ecto would take care of querying the correct table for you. If you don’t want to use ecto schemas it would be easy enough to pattern match on the type field to get the from clause. Something like:
def from_items(:item_one), do: from(i in "item_one", as: :item_one)
def from_items(:item_two), do: from(i in "item_two", as: :item_two)
Cheers!
Trending in Questions
Other Trending Topics
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #elixirconf-us
- #supervisor
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex









