Make Ecto queries more efficient

Question

I'm trying to see if my current user's teams overlap with the passed in user's teams. I have something that works but I'm curious if it could me more efficient. Here is what I have:

user_teams = from(
  t in MyApp.Team,
  left_join: a in assoc(t, :accounts),
  where: p.owner_id == ^user.id or (a.user_id == ^user.id and t.id == a.project_id)
) |> Repo.all

current_user_teams = from(
  t in MyApp.Team,
  left_join: a in assoc(t, :accounts),
  where: t.owner_id == ^current_user.id or (a.user_id == ^current_user.id and p.id == a.project_id)
) |> Repo.all

And then I compare them with:

Enum.any?(user_teams, fn(t) -> t in current_user_teams end)

Again, this suits my needs but seems like there is probably a better way to do this?

Viewless

Do you use either user_teams or current_user_teams later in this request? If so, I guess you need to keep it how it is, but if not you could just do a single query that only gets teams if both the given user and the current user are part of the team. – Akiko

Answer 1

The simplest solution would be just to join these two queries into one and check if resulting query returns anything. So let's do exactly that:

query = from t in MyApp.Team,
  left_join: a in assoc(t, :accounts),
  where: p.owner_id == ^user.id or (a.user_id == ^user.id and t.id == a.project_id),
  where: t.owner_id == ^current_user.id or (a.user_id == ^current_user.id and p.id == a.project_id),
  limit: 1,
  select: true

not is_nil(Repo.one(query))

This will simulate SELECT EXIST (…) query from PostgreSQL (in Ecto 3.0 there will be Repo.exist?/1 function that will do exactly that, related issue).

Duplicated where fragments will be ANDed by default.

Banta