在Prisma中按浏览器名称分组统计关联Redirect总数并过滤

原SQL问题分析 #

你之前的SQL无效的核心原因是：WHERE条件直接限定了Redirect.id = ${input.id}，这会只统计这一条指定的Redirect，而不是该浏览器分组下的所有Redirect，自然得不到预期的总数。

正确SQL实现 #

要满足需求，需要先找到与指定Redirect关联的浏览器，再统计该浏览器同名称分组下的所有Redirect总数，推荐两种SQL写法：

写法一：子查询关联

SELECT
  b."name",
  COUNT(r."id") AS count
FROM
  "public"."Browser" b
JOIN
  "public"."Redirect" r ON b."id" = r."browserID"
WHERE
  b."id" IN (
    SELECT r2."browserID"
    FROM "public"."Redirect" r2
    WHERE r2."id" = ${input.id}
  )
GROUP BY
  b."name"

写法二：多表JOIN

SELECT
  b."name",
  COUNT(r."id") AS count
FROM
  "public"."Redirect" r_target
JOIN
  "public"."Browser" b ON r_target."browserID" = b."id"
JOIN
  "public"."Redirect" r ON b."id" = r."browserID"
WHERE
  r_target."id" = ${input.id}
GROUP BY
  b."name"

Prisma中执行方式 #

使用queryRaw绑定参数执行上述SQL，以JavaScript为例：

const targetRedirectId = input.id;
const result = await prisma.$queryRaw`
  SELECT
    b."name",
    COUNT(r."id") AS count
  FROM
    "public"."Redirect" r_target
  JOIN
    "public"."Browser" b ON r_target."browserID" = b."id"
  JOIN
    "public"."Redirect" r ON b."id" = r."browserID"
  WHERE
    r_target."id" = ${targetRedirectId}
  GROUP BY
    b."name"
`;

执行后即可得到符合要求的格式结果。

原生Prisma分步实现（无SQL） #

如果不想用原生SQL，可以分三步实现（效率略低于SQL，但更符合Prisma风格）：

1. 获取指定Redirect关联的浏览器ID
2. 获取该浏览器的名称（去重）
3. 统计同名称下所有浏览器的Redirect总数

// 1. 获取关联的浏览器ID
const linkedBrowserIds = await prisma.redirect
  .findMany({
    where: { id: input.id },
    select: { browserID: true }
  })
  .then(res => res.map(item => item.browserID));

if (linkedBrowserIds.length === 0) return [];

// 2. 获取对应的浏览器名称（去重）
const targetNames = await prisma.browser
  .findMany({
    where: { id: { in: linkedBrowserIds } },
    select: { name: true }
  })
  .then(res => [...new Set(res.map(item => item.name))]);

// 3. 统计分组总数并格式化结果
const rawResult = await prisma.browser.aggregate({
  where: { name: { in: targetNames } },
  _count: { redirects: true },
  groupBy: ['name']
});

const formattedResult = rawResult.map(item => ({
  name: item.name,
  count: item._count.redirects
}));

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