Owens
Ecto get records where association count is 0
I couldn’t find an answer to this from searching so thought I would make a post about it.
I’m trying to get a list of Users who have not created Office Hours. Essentially, records where the association does not exist or the count is 0.
This article helped but was too advanced for what I’m doing.
The suggestion is to user LEFT OUTER JOIN.
SELECT * FROM rentals
LEFT OUTER JOIN unavailabilities
ON (unavailabilities.rental_id = rentals.id)
WHERE rental_id IS NULL;
Converting this to my use case in Elixir leads to:
query = from u in User,
left_join: o in OfficeHour,
on: [o.user_id: u.id],
where: user_id == nil
I’m not sure if this is correct, or what would be the way to write this using the pipe operators? Any help is appreciated.
Marked As Solved
hauleth
While approach used by @fuelen will work, it is far from optimal one. The best would be using NOT EXISTS, but Ecto do not support it directly, so we need to fallback to fragment:
query =
from u in User, where: fragment("NOT EXISTS (SELECT * FROM office_hours h WHERE h.user_id = ?)", u.id)
This would allow query planner to optimise this query as much as possible.
Also Liked
fuelen
just run the query and see what happens 
Solution in SQL looks correct, but not the Elixir version. Correct snippet is
query = from u in User,
left_join: o in OfficeHour,
on: o.user_id == u.id,
where: is_nil(o.user_id)
If you have declared an association in User struct for :office_hours then query can be simplified a bit to this:
query = from u in User,
left_join: o in assoc(u, :office_hours),
where: is_nil(o.user_id)
and using pipe operators if you like them:
User
|> join(:left, [u], o in assoc(u, :office_hours))
|> where([..., o], is_nil(o.user_id))
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
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.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Courses that'll move you from confusion to "Aha, now I get it!"