martasd
Check a complex "unique" constraint with Ecto
In my Ecto schema, I have a User who has_many Items. An item has a boolean field active:
User module:
schema "users" do
field :name, :string
has_many :items, Item
end
Item module:
schema "items" do
field(:active, :boolean)
belongs_to(:user, User)
end
How can I ensure that a User has at most one item active? Thus, if a user already has an active item, the insertion of another active item should result in an error. The insertion of a new item with active: false should succeed, though.
It seems that exclusion_constraint could be used for that, but I haven’t found any docs showing how to use for anything other than overlapping time intervals.
Marked As Solved
LostKobrakai
A partial unique constraint can do that as well.
create unique_index(:table, [:user_id], where: "active = TRUE")
Also Liked
christhekeele
Adding onto this, you can coerce false to nil in your Item changesets to prevent accidentally writing a false.
I think this approach may work even cleaner.
Either way, after doing one of them you can add :active_item like so to your User schema for easy access:
schema "users" do
field :name, :string
has_many :items, Item
has_one :active_item, Item, where: [active: true]
timestamps()
end
wmnnd
The easiest way to solve this is probably to store nil/NULL instead of false. Then you can use a regular unique index because it only applies to non-NULL values.
martasd
Last Post!
sodapopcan
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Enabling companies to succeed by building software people love.
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
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #api
- #forms
- #metaprogramming
- #security
- #hex
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Error tracking, logs, uptime, and Just Enough APM. Built for developers.
Practical resources that improve the lives of professional developers.
Enabling companies to succeed by building software people love.
The deployment platform built for Elixir: PaaS ease, VPS control.
The team behind Membrane, Popcorn, LiveDebugger. Available for hire.
Courses that'll move you from confusion to "Aha, now I get it!"