如何在Drizzle PostgreSQL中让查询返回唯一资源？

你的查询从embeddings表出发，一个资源（resource）可能关联多条embedding记录，导致join后出现重复的resource数据。要返回唯一资源，核心是对每个resource只保留相似度最高的那条记录，以下是几种可行的解决方案：

方案一：子查询匹配最高相似度 #

通过子查询先获取每个resource对应的最高相似度，再过滤出符合条件的embedding记录：

const similarGuides = await db
  .select({
    resourceId: embeddings.resourceId,
    title: resources.title,
    content: resources.content,
    type: resources.type,
    iconPath: integrations.iconPath,
    name: integrations.name,
    similarity: embeddings.similarity,
  })
  .from(embeddings)
  .innerJoin(resources, eq(embeddings.resourceId, resources.id))
  .innerJoin(integrations, eq(resources.integrationId, integrations.id))
  .where(
    and(
      gt(embeddings.similarity, 0.5),
      eq(
        embeddings.similarity,
        db.select({ maxSim: max(embeddings.similarity) })
          .from(embeddings)
          .where(eq(embeddings.resourceId, resources.id))
      )
    )
  )
  .orderBy(t => desc(t.similarity))
  .limit(4);

方案二：窗口函数分组排名（需数据库支持） #

如果你的数据库支持窗口函数（如PostgreSQL、MySQL 8.0+），可以按resource分组后排名，只取每组第一的记录：

import { rank } from 'drizzle-orm';

const similarGuides = await db
  .with('ranked_embeddings')
  .as(
    db.select({
      resourceId: embeddings.resourceId,
      similarity: embeddings.similarity,
      rnk: rank().over({
        partitionBy: embeddings.resourceId,
        orderBy: desc(embeddings.similarity)
      })
    })
    .from(embeddings)
    .where(gt(embeddings.similarity, 0.5))
  )
  .select({
    resourceId: ranked_embeddings.resourceId,
    title: resources.title,
    content: resources.content,
    type: resources.type,
    iconPath: integrations.iconPath,
    name: integrations.name,
    similarity: ranked_embeddings.similarity,
  })
  .from(ranked_embeddings)
  .innerJoin(resources, eq(ranked_embeddings.resourceId, resources.id))
  .innerJoin(integrations, eq(resources.integrationId, integrations.id))
  .where(eq(ranked_embeddings.rnk, 1))
  .orderBy(t => desc(t.similarity))
  .limit(4);

方案三：从资源表出发分组查询 #

如果不需要保留每条embedding的具体相似度，只需要每个资源的最高相似度记录，可以直接从resources表分组查询：

const similarGuides = await db
  .select({
    resourceId: resources.id,
    title: resources.title,
    content: resources.content,
    type: resources.type,
    iconPath: integrations.iconPath,
    name: integrations.name,
    maxSimilarity: max(embeddings.similarity),
  })
  .from(resources)
  .innerJoin(embeddings, eq(resources.id, embeddings.resourceId))
  .innerJoin(integrations, eq(resources.integrationId, integrations.id))
  .where(gt(embeddings.similarity, 0.5))
  .groupBy(resources.id, integrations.id)
  .orderBy(t => desc(t.maxSimilarity))
  .limit(4);

注意事项 #

• 若similarity是计算生成的字段（非表中直接存在），需要在子查询或窗口函数中替换为对应的计算逻辑。
• 不同数据库对分组字段的要求不同，需确保groupBy包含所有非聚合的返回字段。

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