Prisma同模型双向多对多关系查询优化方案咨询

针对你遇到的同模型双向多对多关系需要手动合并字段的问题，有三种更优的实现方式：

方案一：写入时创建双向关联，统一查询字段 #

在创建艺术家关联时，同时写入两条方向相反的中间表记录，这样查询时只需要调用一个字段就能获取所有相关艺术家：

写入逻辑示例 #

async function addRelatedArtists(artistId1, artistId2) {
  await prisma.relatedArtist.createMany({
    data: [
      { artistId: artistId1, relatedArtistId: artistId2 },
      { artistId: artistId2, relatedArtistId: artistId1 },
    ],
    skipDuplicates: true, // 避免重复创建关联
  })
}

查询示例 #

创建双向关联后，直接查询relatedArtists字段即可：

const artist = await prisma.artist.findUnique({
  where: { id: artistId },
  include: { relatedArtists: { include: { relatedArtist: true } } },
})

const relatedArtists = artist.relatedArtists.map(item => item.relatedArtist)

方案二：用Prisma客户端扩展自动合并字段 #

通过Prisma客户端的扩展功能，给Artist模型添加计算属性，自动合并两个方向的关联结果，无需手动处理：

扩展客户端配置 #

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient().$extends({
  result: {
    artist: {
      allRelatedArtists: {
        // 指定需要依赖的字段
        needs: { relatedArtists: true, artists: true },
        // 自动合并并去重
        compute(artist) {
          const combined = [
            ...artist.relatedArtists.map(ra => ra.relatedArtist),
            ...artist.artists.map(a => a.artist),
          ]
          // 去重避免重复关联
          return Array.from(new Map(combined.map(item => [item.id, item])).values())
        },
      },
    },
  },
})

使用示例 #

查询时直接获取合并后的字段：

const artist = await prisma.artist.findUnique({
  where: { id: artistId },
  include: {
    relatedArtists: { include: { relatedArtist: true } },
    artists: { include: { artist: true } },
  },
})

console.log(artist.allRelatedArtists) // 直接拿到所有相关艺术家

方案三：封装查询函数统一处理 #

如果不想修改模型或扩展客户端，可以封装一个查询函数，内部处理字段合并逻辑，对外提供统一接口：

封装函数示例 #

async function getArtistWithRelated(artistId) {
  const artist = await prisma.artist.findUnique({
    where: { id: artistId },
    include: {
      relatedArtists: { include: { relatedArtist: true } },
      artists: { include: { artist: true } },
    },
  })

  // 合并并去重
  const relatedArtists = Array.from(
    new Map([
      ...artist.relatedArtists.map(ra => [ra.relatedArtist.id, ra.relatedArtist]),
      ...artist.artists.map(a => [a.artist.id, a.artist]),
    ]).values()
  )

  return { ...artist, relatedArtists }
}

使用示例 #

const artist = await getArtistWithRelated(artistId)
console.log(artist.relatedArtists) // 统一字段返回结果

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