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?
supposing you have a many_to_many relation between
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,
You can make it directly from
groupsusers table and match the foreign key for
The efficient way would be a intersect between two selects but I think Ecto doesn’t support that.