postgresql - 如何使用 ecto 查询表达式在 3 个表之间连接和过滤

Question

我有 3 个模式:Post、Author、Assistant。

帖子属于:作者

作者 has_many :posts 和 has_one :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的注意事项:

我放置了 unique 子句，以防您在数据库中为一个作者分配了多个助手(这将表明数据错误)并且结果集中的帖子将被重复。这是因为 has_one 关系只是受约束的 has_many。如果 assistants 表中的 author_id 列有唯一索引，则不需要 unique 子句。如果您没有唯一索引，您可以在迁移中编写以下内容:

create unique_index("assistants", :author_id)

您可能应该对所有 has_one 关系执行此操作。

干杯!