Is it possible to use NOT EXISTS with pipe queries?

Questions / Help
1

I have the following function for finding public trips:

  def for_public(query, true) do
    query
    |> join(:left, [t], p in Permission, on: p.trip_id == t.id, as: :permission)
    |> where([permission: p], p.is_public == true)
  end

How can I get the opposite of this? (Any trip that does not have a permission with is_public)

Is it possible to create a not exists with the pipes?

This is obviously wrong, but what I’m trying to do:

  def for_public(query, false) do
    query
    |> join(:left, [t], p in Permission, on: p.trip_id == t.id, as: :permission)
    |> where(not exists([permission: p], p.is_public == true))
  end

Thanks!

2

What about:

  def for_public(query, false) do
    query
    |> join(:left, [t], p in Permission, on: p.trip_id == t.id and p.is_public == true, as: :public_permission)
    |> where([public_permission: p], is_nil(p.id))
  end

This uses the standard “check for nonexistence” LEFT JOIN / IS NULL combo

4 Likes
3 
  def for_public(query, true) do
    query
    |> join(:left, [t], p in Permission, on: p.trip_id == t.id and p.is_public, as: :permission)
    |> where([permission: p], is_nil(p.id))
  end

Add the permission filter to the join criterion, and then filter on it not existing.

3 Likes
4

Oh nice, thank you both! These are much cleaner than my attempt without pipes.