0

I have a User model that has many Membership. Membership has a field called group_id.

I want to get a list of Users who have no memberships with the group_id field not equal to 1.

I tried this

from u in User, join: m in assoc(u, :memberships), where: m.group_id != 1

I have 3 users in my db and one of them have a membership with group_id = 1. So I am expecting my query to return 2 users who don't have the membership. But it return empty array.

shankardevy
  • 4,290
  • 5
  • 32
  • 49

1 Answers1

0

If you want to explicitly fetch the users without a membership, you need to use a left join and find where the group_id is nil:

from u in User,
left_join: m in assoc(u, :memberships),
where: is_nil(m.group_id))

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? explains the differences between joins (left vs inner in this case.)

Community
  • 1
  • 1
Gazler
  • 83,029
  • 18
  • 279
  • 245
  • `where: is_nil(m.group_id)` return 2 items, but `where: m.group_id != 1` return empty array. Maybe it's silly but I can't understand this behaviour. – shankardevy Feb 03 '16 at 16:55