Terbium-135
Given a schema named group with another embedded schema like this:
schema "groups" do
[...]
embeds_many :absences, Absence, on_replace: :delete do
field :reason, :string
field :first_day, :date
field :last_day, :date
field :position, :integer
end
[...]
end
With the database field (as defined in a migration):
` add :absences, :map`
I.e. I get the following DB data for a single element:
[{"id": "48d308ec-e0d6-4462-a58e-95f7f47f8308", "reason": "sabbatical", "last_day": "2024-12-31", "position": 0, "first_day": "2024-01-01"}]
How I am supposed to query with ecto to exclude a group if a given date is in any element of the jsonb array?
I got stuck trying to convert the jsonb to a recordset for further usage:
from g in Backend.Schema.Group,
inner_lateral_join:
absence in fragment(
"SELECT * FROM jsonb_to_recordset(to_jsonb(?)) as absence(first_date date, last_date date)",
g.absences
),
[?]
Not asking for a solution but a few pointers in the right direction would be highly appreciated
Where Next?
Trending in Questions
Other Trending Topics
Latest Phoenix Threads
Latest on Elixir Forum
Sponsor Spotlight
Bringing learning, work and compliance together since 1987.
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
- #blog-post
- #phoenix_html
- #iex
- #graphql
- #ai
- #genstage
- #elixirconf-us
- #websockets
- #supervisor
- #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!"
First 6 of 6 Posts
benwilson512
I realize this isn’t exactly what you’re asking but this sort of thing definitely feels like it would be better modeled by an actual table at the database level. At that point you can just use a left join between the groups and the absences, where you use the join condition to filter out the absences.
al2o3cr
+1 for “use a regular table for this”; query performance will also be much faster.
Either way (separate table for
absencesorjsonb_to_recordset), you’re looking for a “non-existence” query:WHEREto only include rows that gotNULLs from the outer-joinlinusdm
TIL this trick has a name. Cool! I use this pattern all the time.
jswanner
The name I know for this technique is “anti-join”
Terbium-135
Yes. I agree. Sounds ‘cleaner’ to me too.
That DB layout was given to me like it is and while I was looking at that JSONB field and its content in the DB I was scratching my head how to handle this.
Terbium-135
Thanks for the general layout!
I am still curious how it’s been done with that JSONB field just for learning purpose.
If I have time I will give it a try.
Looks like ecto’s embed_one, embed_many is meant to be used with the preload function
In any case, thanks all for chiming in
Creating an extra table for now