sql - 在Ecto v2.0中使用LATERAL联接

我正试图加入一个最新的评论后记录，如：

comment = from c in Comment, order_by: [desc: c.inserted_at], limit: 1

post = Repo.all(
  from p in Post,
  where: p.id == 123,
  join: c in subquery(comment), on: c.post_id == p.id,
  select: [p.title, c.body],
  limit: 1
)

生成此SQL的：

SELECT p0."title",
       c1."body"
FROM   "posts" AS p0
       INNER JOIN (SELECT p0."id",
                          p0."body",
                          p0."inserted_at",
                          p0."updated_at"
                   FROM   "comments" AS p0
                   ORDER  BY p0."inserted_at" DESC
                   LIMIT  1) AS c1
               ON c1."post_id" = p0."id"
WHERE  ( p0."id" = 123 )
LIMIT  1

它只是返回nil。如果我删除on: c.post_id == p.id它将返回数据，但显然它将返回所有帖子的最新评论，而不是所讨论的帖子。
我做错什么了？修复方法可能是使用LATERAL联接子查询，但我无法确定是否可以将p引用传递到subquery中。
谢谢！

最佳答案

这个问题是由这里的limit: 1引起的：

comment = from c in Comment, order_by: [desc: c.inserted_at], limit: 1

由于结果查询是SELECT * FROM "comments" AS p0 ORDER BY p0."inserted_at" DESC LIMIT 1，它只返回任何帖子的最新评论，而不是我正在查询的帖子。
仅供参考，查询大于150ms，包含约200000个注释行，但通过一个简单的索引将其减少到约12ms：

create index(:comments, ["(inserted_at::date) DESC"])

值得注意的是，虽然这个查询可以返回有问题的post和最近的注释，但如果删除$number_of_comments，它实际上会返回limit: 1行。假设您想要检索数据库中的所有100篇文章，其中包含每个文章的最新评论，并且数据库中有200000条评论，那么这个查询将返回200000行。相反，您应该使用LATERAL连接，如下所述。
.
更新
不幸的是ecto doesn't support LATERAL joins right now。
一个extofragment在这里工作得很好，但是join查询将片段包装在附加的括号中（即INNER JOIN (LATERAL (SELECT …))），这不是有效的SQL，所以现在必须使用原始SQL：

sql = """

  SELECT p."title",
         c."body"
  FROM   "posts" AS p
         INNER JOIN LATERAL (SELECT c."id",
                                    c."body",
                                    c."inserted_at"
                     FROM   "comments" AS c
                     WHERE  ( c."post_id" = p."id" )
                     ORDER  BY c."inserted_at" DESC
                     LIMIT  1) AS c
                 ON true
  WHERE  ( p."id" = 123 )
  LIMIT  1

"""

res = Ecto.Adapters.SQL.query!(Repo, sql, [])

此查询在同一数据库上以小于1毫秒的速度返回。
注意，这不会返回你的exto模型结构，只是Postgrex的原始响应。

lateral

sql - 在Ecto v2.0中使用LATERAL联接