基于Prisma加载关联技能数据：无需建立多对多关系的方案

使用MySQL作为底层数据库，搭配Prisma ORM。现有两张未关联的表：candidates和skills，既无关联表，skills表也没有candidate_id字段。两表仅通过candidates表中的JSON类型字段primary_skills关联，该字段存储技能ID数组，例如：[34, 87, ..., 96]。

希望查询候选人表时，primary_skills字段返回完整的技能数据而非仅技能ID数组，示例如下：

{
    candidate_id: 1,
    primary_skills: [
        {
            id: 1,
            skill_name: "Dummy skill 1"
        },
        {
            id: 2,
            skill_name: "Dummy skill 2"
        }
    ]
}

而非：

{
    candidate_id: 1,
    primary_skills: [1, 2]
}

请问除了建立显式/隐式多对多关系外，是否有其他实现方案？（现有表数据量庞大，建立多对多关系需进行大量数据迁移）

可行解决方案 #

1. 使用Prisma原生SQL查询关联数据 #

借助MySQL的JSON_CONTAINS函数关联两张表，通过Prisma的$queryRaw编写原生SQL，直接返回包含完整技能数据的结果。

示例代码：

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function getCandidatesWithSkills() {
  const candidates = await prisma.$queryRaw`
    SELECT 
      c.*,
      JSON_ARRAYAGG(JSON_OBJECT('id', s.id, 'skill_name', s.skill_name)) AS primary_skills
    FROM candidates c
    LEFT JOIN skills s ON JSON_CONTAINS(c.primary_skills, CAST(s.id AS JSON))
    GROUP BY c.candidate_id
  `;
  return candidates;
}

通过LEFT JOIN关联skills表，用JSON_CONTAINS匹配技能ID，再用JSON_ARRAYAGG和JSON_OBJECT将匹配到的技能数据聚合为JSON数组，替换原有的ID数组字段。

2. 利用Prisma计算字段（Prisma 4.16+） #

使用Prisma的计算字段特性，在Schema中定义虚拟字段，通过自定义SQL返回关联的技能数据。

首先修改schema.prisma中的Candidate模型：

model Candidate {
  candidate_id Int      @id @default(autoincrement())
  // 保留原JSON字段并重命名，避免与计算字段冲突
  primary_skill_ids Json
  // 定义计算字段，标记为不映射到实际数据库列
  primary_skills Json    @ignore @db.Json
}

查询时通过select配合Prisma.sql填充计算字段：

async function getCandidatesWithSkills() {
  return prisma.candidate.findMany({
    select: {
      candidate_id: true,
      primary_skills: Prisma.sql`(
        SELECT JSON_ARRAYAGG(JSON_OBJECT('id', s.id, 'skill_name', s.skill_name))
        FROM skills s
        WHERE JSON_CONTAINS(c.primary_skill_ids, CAST(s.id AS JSON))
      )`
    }
  });
}

3. 应用层手动关联数据 #

先批量查询所有候选人，提取所有技能ID后批量查询技能数据，最后在代码中手动映射关联关系。

示例代码：

async function getCandidatesWithSkills() {
  // 1. 查询所有候选人
  const candidates = await prisma.candidate.findMany();
  if (candidates.length === 0) return [];

  // 2. 提取并去重所有技能ID
  const allSkillIds = [...new Set(
    candidates.flatMap(c => JSON.parse(c.primary_skills as string) as number[])
  )];

  // 3. 批量查询对应技能
  const skills = await prisma.skill.findMany({
    where: { id: { in: allSkillIds } }
  });
  const skillMap = new Map(skills.map(s => [s.id, s]));

  // 4. 映射技能数据到候选人
  return candidates.map(c => ({
    ...c,
    primary_skills: (JSON.parse(c.primary_skills as string) as number[])
      .map(id => skillMap.get(id))
  }));
}

该方案无需修改数据库或Schema，完全在应用层处理，适合数据量未达到极端量级的场景。

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