Best way to join results of two queries?

Hey guys, I have two queries:

  1. Returns all public groups in a team.
  2. Returns all teams the user is invited to participate in.

Both queries work really well but I’d like to merge the results together to provide a single cohesive list with no duplicate teams.

Any suggestions?

def available_teams(user_id) do
  user = Repo.get(User, user_id)
  team = Repo.get(Team, user.team_id)

  public_teams =
    from g in Group,
    where: g.team_id == ^team.id,
    where: g.public == ^true

  participating_teams =
    from gu in GroupUser,
    join: g in Group,
    where: gu.group_id == g.id,
    where: gu.user_id == ^user.id,
    select: g

  Repo.all(public_teams + participating_teams???)
end

Appreciate the help!

I tweaked the query and it’s returning groups that the user is participating in, but not any of the public groups.

def available_groups(user_id) do
  user = Repo.get(User, user_id)
  team = Repo.get(Team, user.team_id)

  groups =
    from g in Group,
    join: gu in GroupUser,
    join: u in User,
    where: g.id == gu.group_id,
    where: gu.user_id == u.id,
    where: g.public == ^true or gu.user_id == ^user_id

  Repo.all(groups)
end

Is this because of the join: gu in GroupUser?

Ended up with this. Is this dumb lol I’m not so handy with SQL.

def available_groups(user_id) do
  user = Repo.get(User, user_id)
  team = Repo.get(Team, user.team_id)

  public_groups =
    from g in Group,
    where: g.team_id == ^team.id,
    where: g.public == ^true

  participating_groups =
    from gu in GroupUser,
    join: g in Group,
    where: gu.group_id == g.id,
    where: gu.user_id == ^user.id,
    select: g

  public_groups = Repo.all(public_groups)
  participating_groups = Repo.all(participating_groups)
  result_groups = public_groups ++ participating_groups

  result_groups
  |> Enum.uniq
end

Take a look at left_join and the other join types. ^.^

1 Like

If it’s not too much trouble, can you write an example for this specific instance. The examples in the Ecto documentation shows simple join and left_join but the thing is here it’s like a conditional, join OR public=true. I can’t quite wrap my head around it.

This is entirely written in-post and probably not efficient but something like this:

query =
  from g in Group,
  left_join: gu in GroupUser, on: gu.group_id == g.id and gu.user_id == ^user.id
  where: is_nil(gu.id) or (g.public == ^true and g.team_id == ^team.id),
  select: g

Or something like that.

A left_join is an outer join on the left side (the ‘from’, so ‘Group’ here), and the left (Group) must always exist, but what it binds to on the right (GroupUser) can be null/empty. As the above is made could have duplicates if the user is both joined and it is public, but you could refine the query to fix that if you want or just Enum over it after to remove duplicates.

2 Likes

Thanks @OvermindDL1 appreciate your help! I’ll review this and try to grok it. Still looks unfamiliar to me haha.

1 Like