如何使用ecto查询表达式在3个表之间联接和筛选

Question

我有三个模式：Post、Author、Assistant。

Post belongs_to :author

Author has_many :posts和has_one :assistant

Assistant belongs_to :author

我想构建一个只获取Posts的查询，其中Author有一个Assistant。

到目前为止，我只能够加入Author到Post。我不知道如何根据Post的Author's Assistant的存在来查询Assistant。

Post
|> join(:inner, [p], a in assoc(p, :author))
|> Repo.all()

编辑1

我认为我已经取得了一些进展，这个查询似乎正在处理数据库中的几个记录，但不确定这是否是传统的方法。

Post
|> join(:inner, [p], a in assoc(p, :author), as: :author)
|> join(:left, [author: a], asst in assoc(a, :assistant), as: :assistant)
|> where([assistant: asst], not is_nil(asst.id)))
|> Repo.all()

Answer 1

我认为在这种情况下不需要左联接。您应该能够在所有表上进行内部连接，因为内部连接将指示一个助手的存在，并排除了在没有助手的情况下获得作者的任何可能性。你应该能够写这样的东西：

query = from(p in Post,
  join: a in Author,
  on: a.id == p.author_id,
  join: ast in Assistant,
  on: ast.author_id == a.id,
  # See notes below about the distinct
  distinct: p.id
)

Repo.all(query)

因为您在所有表上都进行了内部连接，所以它指示一个帖子必须有一个作者，而一个作者必须有一个助手才能在这个查询中得到一个结果行。如果你想做一些事情，如找出所有的帖子，有一个作者没有助手，你会想离开加入。

关于distinct**:**的注记

如果您将多个助理分配给数据库中的一个作者(这将指示糟糕的数据)，并且结果集中的帖子将被重复，我将使用distinct子句。这是因为has_one关系只是一个受限的has_many。如果在author_id表中的assistants列上有唯一索引，则不需要distinct子句。如果您没有唯一的索引，则可以在迁移中编写此索引：

create unique_index("assistants", :author_id)

对于所有的has_one关系，您可能都应该这样做。

干杯!

Answer 2

在这种情况下，这可能是过度的，但是将这些查询解耦到子查询总是更容易一些。

authors_with_assistants =
  from a in Author, where: not is_nil(a.assistant_id)

或

authors_with_assistants =
  from a in Author,
  left_join: asst in assoc(a, :assistant)

和

posts =
  from p in Post,
    join: a in subquery(authors_with_assistants),
    on: p.author_id == a.id

Repo.all(posts)

此外，使用条件连接后一个条件也可以(这意味着您在Edit1部分中的查询看起来很好)。