基于Postgres/Kysely按traitId分组并按组最小createdAt排序创建视图

纯SQL实现 #

通过子查询计算每个traitId组的最早创建时间，关联原表后按组的最早时间、行自身创建时间排序，创建视图：

CREATE VIEW InspirationView AS
SELECT 
    i.id,
    i.createdAt,
    i.bigPaintId,
    i.traitId,
    i.content
FROM Inspiration i
INNER JOIN (
    SELECT traitId, MIN(createdAt) AS group_min_createdAt
    FROM Inspiration
    GROUP BY traitId
) g ON i.traitId = g.traitId
ORDER BY g.group_min_createdAt ASC, i.createdAt ASC;

Kysely客户端实现 #

用Kysely查询构建器对应上述SQL逻辑，完成视图创建：

await db.schema
  .createView("InspirationView")
  .as((queryBuilder) => 
    queryBuilder
      .selectFrom("Inspiration as i")
      .innerJoin(
        // 子查询获取每个traitId的最早创建时间
        queryBuilder
          .selectFrom("Inspiration")
          .select([
            "traitId",
            queryBuilder.fn.min("createdAt").as("group_min_createdAt")
          ])
          .groupBy("traitId")
          .as("g"),
        "i.traitId",
        "g.traitId"
      )
      .select([
        "i.id",
        "i.createdAt",
        "i.bigPaintId",
        "i.traitId",
        "i.content"
      ])
      // 先按组的最早创建时间排序，组内再按行自身创建时间排序
      .orderBy("g.group_min_createdAt", "asc")
      .orderBy("i.createdAt", "asc")
  )
  .execute();

逻辑说明 #

1. 子查询通过GROUP BY traitId和MIN(createdAt)计算每个traitId组的最早创建时间group_min_createdAt
2. 将原表与子查询关联，让每一行都能获取到所属组的最早创建时间
3. 排序时优先按group_min_createdAt升序，确保同一traitId的行归为一组且按组的创建先后排列；组内再按行自身的createdAt升序排列，完全匹配示例中的排序规则

内容的提问来源于stack exchange，提问作者paolodelfino