使Ecto查询更高效

问题描述：

我正在尝试查看当前用户的团队与传入的用户团队是否重叠.我有一些可行的方法，但我想知道是否可以提高效率.这是我所拥有的:

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

然后我将它们与:

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?

答

最简单的解决方案是将这两个查询合并为一个，并检查结果查询是否返回任何内容.因此，我们要完全做到这一点:

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))

这将模拟来自PostgreSQL的SELECT EXIST (…)查询(在Ecto 3.0中，将有Repo.exist?/1函数完全可以做到这一点，

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).

重复的where片段将默认为AND.

相关推荐