Ecto get records where association count is 0

Questions / Help
, ,
1

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.

2

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
3

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
4

Thanks so much for this! Wondering whether this is still the case, or whether you know of a better way to do this in Ecto now (3 years later)?

5

The same can be written in DSL now without fragments

6

Thanks for the reply, @fuelen! Could you possibly link the relevant docs? I see exists/1 but nothing that corresponds to “NOT EXISTS” as far as I can tell.

7

If you try to do comparisons with nil in a query like “x.id == nil” it will tell you to use is_nil in the error so I assume thats the “correct” method.

You can also externalise things with dynamics like below. I needed to get tag IDs from a list, so “t.id in ^tags” but you can’t use is_nil or == nil for lists as far as I’m aware. So I used dynamic to externalize the condition where the list was empty.

  def list_user_posts(current_user, user, params) do

    tag_condition =
      if tags !== nil do
        dynamic([_p, _pt, t], t.id in ^tags)
      else
        dynamic([p, pt, t], (not is_nil(t.id) or t.id > 0))
      end
          
      Repo.all(
        from p in Post,
        left_join: pt in Post_Tag, on: p.id == pt.post_id,
        left_join: t in Tag, on: t.id == pt.tag_id,
        where: p.user_id == ^user.id 
          and  p.inserted_at >= ^time_period 
          and  fragment("lower(?) like lower(?)", p.title, ^"%#{query}%"),
        where: ^tag_condition,
        order_by: ^order_by,
        distinct: true,
        preload: [:user, :board, :comments, :tags]
      )    
  end
8

@fuelen nevermind! I think I figured it out. I didn’t consider that you could just use not/1 with exists/1 in this way, but the docs seem to suggest that you can: Ecto.Query.API — Ecto v3.11.0

So I think I should be all good. Thanks again for the reply earlier!