Ecto query for parent table by values from child

Questions / Help
1

Hi. I have a question, on how to get the count of a parent, where the I need to check the children. Here is my approach:

def direct_chat_exists?(sender, receiver) do
    query = from g in Group,
      join: u in UserGroup,
      on: g.id == u.group_id,
      where: g.group_type == "super_grouop",
      where: u.user_id in [^sender, ^receiver],
      select: count(g.id)
Repo.one(query)
end

So I need the count of the groups, which have sender and receiver set as user_id in their UserGroups.
So every Group which has sender and receiver in their UserGroup.

Any Idea?

2

I’d recommend thinking about how to do this with straight SQL and then translating into what is available in Ecto. In SQL I think you’d do:

SELECT COUNT(*) 
FROM group g 
WHERE g.id in 
(SELECT g.id 
FROM group 
JOIN user_group ug ON (g.id = ug.group_id) 
WHERE g.group_type = 'super_group' and u.user_id in ['sender', receiver'])

So…in Ecto you need to take that IN clause and turn it into a query and then join your Group with that built query.

def direct_chat_exists?(sender, receiver) do
  sub_query = build_subquery(sender, receiver)
  query = from g in Group,
               join: s in subquery(sub_query), on: s.id == g.id,
               select: count(g.id)
  Repo.one(query)
end

def build_subquery(sender, receiver)
    from g in Group,
    join: u in UserGroup,
    on: g.id == u.group_id,
    where: g.group_type == "super_grouop",
    where: u.user_id in [^sender, ^receiver],
    select: g.id
end

I guess that’s how I’d approach it anyway.

3

Hm, still the same result as with my query. My scenario is, that I have a group, with 2 Usergroups -> Sender =1, Receiver =2. Now I want to add a new Group with usergroups -> Sender =1 and Receiver =3. I want to get a count of the groups with Sender =1 and Receiver = 3, but both queries return a count of 1, because in [^sender, ^receiver] returns one count, when Sender=1 is present, but needs to return a count of 1 only, if user_id with 1 and 3 are present as Usergroups. I don’t want use the workaround and get a result with the group_id and the user_ids and compare the result inside elixir code with the two parameters.

4

Sounds like you are trying to express something like this (untested obviously):

from s in UserGroup,
join: g in Group, on: s.group_id = g.id,
join: r in UserGroup, on: g.id = r.group_id,
where: s.user_id == ^sender,
where: g.group_type == "super_group",
where: r.user_id == ^receiver
select: count(g.id)
5

Ai, I was wondering which join I missed. Thought about joining UserGroup with UserGroups, but your solution passed my test :blush: Once again: Thanks a lot :bowing_man: