如何用Prisma查询去过美国和中国的用户？附原生SQL写法

背景说明 #

我正在构建一个记录用户旅行目的地的数据库，采用多对多关系设计，对应的Prisma数据模型如下：

model Person {
  id String  @id @default(cuid())
  name String
  personCountry PersonCountry[]
}

model Country {
  id String  @id @default(cuid())
  name String
  personCountry PersonCountry[]
}

model PersonCountry{
  id String  @id @default(cuid())
  person Person @relation(fields: [personId], references: [id], onDelete: onCascade)
  personId String
  country Country @relation(fields: [countryId], references: [id], onDelete: onCascade)
  countryId String
}

示例数据：

const persons = [{ id: "1", name: "John" }, { id: "2", name: "Jack" }]
const countries = [{ id: "1", name: "USA" }, { id: "2", name: "China" }]

关联数据（John去过美国和中国）：

personCountry: [{ personId: "1", countryId: "1" }, { personId: "1", countryId: "2" }]

需求 #

查询同时去过美国和中国的用户，分别给出Prisma查询语句和原生SQL语句。

Prisma 查询语句 #

方案一：多条件组合筛选 #

通过AND和some组合，确保用户同时关联两个目标国家：

const targetUsers = await prisma.person.findMany({
  where: {
    AND: [
      {
        personCountry: {
          some: {
            country: { name: "USA" }
          }
        }
      },
      {
        personCountry: {
          some: {
            country: { name: "China" }
          }
        }
      }
    ]
  },
  // 可选：包含关联的国家信息
  include: {
    personCountry: {
      include: { country: true }
    }
  }
});

方案二：聚合计数验证 #

如果需要适配更多目标国家，可通过聚合查询统计用户关联的目标国家数量：

const targetCountries = ["USA", "China"];
const targetUsers = await prisma.person.findMany({
  where: {
    personCountry: {
      some: {
        country: { name: { in: targetCountries } }
      }
    }
  },
  include: {
    personCountry: { include: { country: true } }
  },
  having: {
    personCountry: {
      _count: {
        country: { name: { in: targetCountries } }
      },
      equals: targetCountries.length
    }
  }
});

原生SQL 查询语句 #

方案一：分组计数法 #

通过关联表分组，筛选出关联目标国家数量等于预期的用户：

SELECT p.id, p.name
FROM Person p
JOIN PersonCountry pc ON p.id = pc.personId
JOIN Country c ON pc.countryId = c.id
WHERE c.name IN ('USA', 'China')
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT c.id) = 2;

注：COUNT(DISTINCT c.id)用于避免同一用户多次访问同一国家导致计数错误。

方案二：多次关联筛选法 #

通过多次关联目标国家，直接筛选出同时满足两个条件的用户：

SELECT DISTINCT p.id, p.name
FROM Person p
JOIN PersonCountry pc_usa ON p.id = pc_usa.personId
JOIN Country c_usa ON pc_usa.countryId = c_usa.id AND c_usa.name = 'USA'
JOIN PersonCountry pc_china ON p.id = pc_china.personId
JOIN Country c_china ON pc_china.countryId = c_china.id AND c_china.name = 'China';

注：添加DISTINCT避免重复结果（若用户多次关联同一国家）。

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