I have this need of a field in one of my schema that should be nominal but that could also be ordered. Think of a difficulty/level value say beginner/intermediate/advanced that you could query by its order.
The way I first though of it was to make the field refer to a integer column in the database and then use Ecto custom types to map numeric values to strings or atoms, but then it occurred to me that this kind of need is probably more common and there should be a dedicated way to do this?
Or maybe even using integers for that is not a good idea at all, I’m not sure…
Any insight is appreciated
Storing your data and querying it ordered are imo two separate concerns. For the first one I would suggest the fresh ecto 3.5 Ecto.Enum. For the second you’d have another table on the db, which has the levels and their ordering and if you need items ordered join that table. This way you’re flexible to change the ordering if needed.
This was my first thought as I’ve always done it that way… but knowing it’s going to be a field with very few options it seems like one of those cases where people say you should denormalize…
Thanks for your input
If you’re using Postgres, an enum will sort according to the order it’s defined in.
There are some restrictions / gotchas with enums (removing values is HARD) and adding new values requires a database migration, but if your list of values is very stable it’s a good choice.
Check out EctoEnum.
I was pretty sure these values were stable but this all caps HARD made me second guess myself a bit haha
Great stuff, thank you for pointing it out!