postgresql - 在嵌套的 array_agg 查询中排序

Question

此查询的目标是从 communities 表中提取所有内容，以及从连接中提取 jsonb 对象的 array_agg。因此，这在未排序的情况下工作正常:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id))
    from documents as conversations
      join community_permissions on community_permissions.document_id = conversations.id
        where conversations.published = true
        and community_permissions.community_id = communities.id
  ) as conversations
  from communities where communities.id = 110;

这将返回一行，其中包含 communities 中列的普通列，以及一个类似于 [{ id: 1 }, { id: 2 } ...] 的 json 数组 在 conversations 列中。一切都很好，查询工作得很好，我可以在 SQL 中以声明方式一次获取一行及其重要关系。我什至可以过滤来自不同表的连接的返回值(此列表中仅返回已发布的对话)。

我现在想要做的是根据文档表中的属性(别名为上面的对话)对对话进行排序。天真的方法:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id))
    from documents as conversations
      join community_permissions on community_permissions.document_id = conversations.id
        where conversations.published = true
        and community_permissions.community_id = communities.id
        order by conversations.updated_at desc
  ) as conversations
  from communities where communities.id = 110;

不起作用 - 列浏览器希望 conversations.updated_at 值位于 group_by 子句中。如果我像这样添加一个 group by 子句:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id))
    from documents as conversations
      join community_permissions on community_permissions.document_id = conversations.id
        where conversations.published = true
        and community_permissions.community_id = communities.id
        group by conversations.updated_at
        order by conversations.updated_at desc
  ) as conversations
  from communities where communities.id = 110;

看起来 array_agg 不再实际聚合响应 - 我收到错误消息“用作表达式的子查询返回的行不止一行”。我可以在 order by 之后放置一个 limit 1，查询就会执行，但是我只会在 array_agg 列中获得一个对话——至少是最近的一个。所以看起来 array_agg 实际上并没有聚合到一个数组中，可以这么说。

我可以通过将整个事物拆开并使用 CTE 来执行此查询:

with q1 as (
  select conversations.id as id, community_permissions.community_id as community_id
  from documents as conversations
  join community_permissions on community_permissions.document_id = conversations.id
  where conversations.published = true order by conversations.updated_at desc
)
select communities.id, (
  select array_agg(jsonb_build_object('id', q1.id))) as conversations 
  from communities join q1 on q1.community_id = communities.id
  where communities.id = 110
  group by communities.id;

但这非常低效，解释器表示每次运行查询时它都会对整个对话表进行排序，并且任何给定的社区只有一小部分对话表通过 community_permissions 与其共享。

这也不令人满意，因为我正在尝试模块化我的查询，以便后端可以决定，“嘿，你将需要这些值”并将它们推送到 SQL 查询中，并在一个查询中一次性完成所有操作，而不是拉动社区然后手动查询每个关系(通常不止一个 - 社区有成员、对话、关注者等)。最坏的情况下，我可以这样做 - 只需处理后端节点层中的额外提取并在那里组装，但将它们全部放在子查询中似乎更优雅(直到我需要这种排序功能)。

Answer 1

其中一种情况是“在 stackoverflow 上写了一篇很长的帖子，十分钟后才找到答案”。

为了将来引用，这个技巧是我把订单放在了错误的地方。这有效:

select communities.*,
  (
    select array_agg(jsonb_build_object('id', community_permissions.document_id) order by conversations.updated_at desc)
    from documents as conversations
      join community_permissions on community_permissions.document_id = conversations.id
      where conversations.published = true
      and community_permissions.community_id = communities.id
  ) as conversations
  from communities where communities.id = 110;

请注意，order by 现在位于 array_agg 调用中，而不是在子查询中。