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.

just run the query and see what happens :slight_smile:
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))
1 Like

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.

2 Likes