Can I convert a NOT IN (subquery) to a join in Ecto?

Hi all,

I’ve got the following SQL statement that I’d like to Ecto-ize

SELECT * from organizations WHERE id NOT IN (SELECT id from memberships where user_id = 123)

I’ve got it working with fragment:

    q = from o in Organization,
       where: o.private == false and fragment("id NOT IN (select id from memberships where user_id = ?)", ^id)

    Repo.all(q)

I know that we can’t use subquery in a WHERE clause, and using joins are recommended, but I can’t figure out how to convert the above query into a JOIN.

Thanks,

Andrew

Just inverse the condition and you shouldn’t even need a subquery anymore

from o in "organisations",
  join: m in "memberships", on: o.id == m.id,
  where: m.user_id != 123
5 Likes

This is the best way to do it. If you really wanted to keep the previous structure though you can just left_join it and test if a column on the right is null though to enforce it doesn’t exist. :slight_smile: