Ecto Query - Users with common Group

Questions / Help
1

I have three tables: users, groups and a groupsusers join table (user_id, group_id).

Given two user ids, I’d like to know if these two users both belong to a common group (any group, not a specific group).

Is this something I can express with an Ecto query, or would I need to drop in to raw SQL for this?

Thanks

2

supposing you have a many_to_many relation between User and Group I think something like that would do:

from u1 in User,
inner_join: g in assoc(u, :groups),
inner_join: u2 in assoc(g, :users),
where: u1.id == ^user1_id and u2.id == ^user2_id,
distinct: true,
select: g

You can make it directly from groupsusers table and match the foreign key for groups

The efficient way would be a intersect between two selects but I think Ecto doesn’t support that.

1 Like