使用Prisma筛选用户分配选民：匹配最新BlockAgent承诺状态

当前使用Prisma获取已分配给用户的选民数据时，除承诺（pledge）筛选逻辑外，其余筛选条件均正常生效。需求为：筛选出VoterPledge表中pledgeType为"BlockAgent"且id最大的行，其pledgeStatus与filter.pledge匹配的选民，不匹配的选民不纳入结果集。

现有代码 #

筛选条件构建代码

if(filter) {
      if(filter.atoll) {
        where.voter = { atoll: { contains: filter.atoll, mode: 'insensitive' } }
      }

      if(filter.island) {
        where.voter = { island: { contains: filter.island, mode: 'insensitive' } }
      }

      if(filter.currentIsland) {
        where.voter = { currentIsland: { contains: filter.currentIsland, mode: 'insensitive' } }
      }

      if(filter.politicalParty) {
        where.voter = { politicalParty: { contains: filter.politicalParty, mode: 'insensitive' } }
      }

      if(filter.belongsToBlock) {
        const input = filter.belongsToBlock.trim().toLowerCase()

        where.voter = {
          belongsToBlock: {
            name: {
              equals: input,
              mode: 'insensitive',
            }
          }
        }

      }

      if(filter.pledge) {

        const maxIdSubquery = await this.prisma.$queryRaw<number>`
          SELECT MAX("id")
          FROM "VoterPledge" AS "vp"
          JOIN "Voter" AS "v" ON "v"."id" = "vp"."voterId"
          JOIN "userVoterAssignment" AS "uva" ON "v"."id" = "uva"."voterId"
          WHERE "uva"."id" = "userVoterAssignment"."id"
          AND "vp"."pledgeType" = 'BlockAgent'
        `;

        where.voter = {
          pledges: {
            some: {
              id: maxIdSubquery[0].max,
              pledgeStatus: {
                equals: filter.pledge,
                mode: 'insensitive',
              },
            },
          },
        };

      }
    }

数据查询代码

const getAssigned = await this.prisma.userVoterAssignment.findMany({
        where: where,
        skip: limit ? skip : undefined,
        take: limit || undefined,
        include: {
          voter: {
            include: {
              belongsToBlock: true,
              pledges: {
                include: {
                  createdByUser: true
                },
                take: 1,
              },
              callMeetups: {
                include: {
                  createdByUser: true,
                }
              },
              createdByUser: true,
              requests: {
                include: {
                  createdByUser: true,
                }
              },
              assignedToUsers: {
                include: {
                  user: true,
                },
                where: {
                  isActive: true,
                }
              },
            },
          },
        },
        orderBy: [
          { createdAt: 'desc'},
          { voter: {
            sumaaruNo: 'asc',
          },},
        ],
      })

问题分析 #

1. 多筛选条件覆盖问题：每次直接赋值where.voter = {...}会覆盖之前设置的voter条件，导致同时使用多个筛选条件（比如atoll+pledge）时，只有最后一个条件生效。
2. 承诺筛选子查询错误：提前执行的$queryRaw无法关联外层查询的userVoterAssignment.id，会返回全局最大的pledge id而非每个选民对应的最大id，最终筛选逻辑完全偏离需求。

修复方案 #

步骤1：合并多筛选条件

通过对象累加的方式保存voter条件，避免覆盖：

// 初始化空的where对象
const where = {};
if(filter) {
  const voterConditions = {};
  
  if(filter.atoll) {
    voterConditions.atoll = { contains: filter.atoll, mode: 'insensitive' };
  }

  if(filter.island) {
    voterConditions.island = { contains: filter.island, mode: 'insensitive' };
  }

  if(filter.currentIsland) {
    voterConditions.currentIsland = { contains: filter.currentIsland, mode: 'insensitive' };
  }

  if(filter.politicalParty) {
    voterConditions.politicalParty = { contains: filter.politicalParty, mode: 'insensitive' };
  }

  if(filter.belongsToBlock) {
    const input = filter.belongsToBlock.trim().toLowerCase();
    voterConditions.belongsToBlock = {
      name: { equals: input, mode: 'insensitive' }
    };
  }

  // 有条件时才赋值给where.voter
  if(Object.keys(voterConditions).length > 0) {
    where.voter = voterConditions;
  }

步骤2：正确实现承诺筛选逻辑

使用Prisma子查询匹配每个选民最新的BlockAgent承诺状态：

if(filter.pledge) {
    // 子查询：获取每个选民的BlockAgent类型承诺的最大id
    const maxPledgeSubquery = this.prisma.voterPledge.groupBy({
      by: ['voterId'],
      _max: { id: true },
      where: { pledgeType: 'BlockAgent' }
    });

    // 合并承诺筛选条件，保留原有voter条件
    where.voter = {
      ...where.voter,
      pledges: {
        some: {
          id: {
            in: maxPledgeSubquery.select({ _max_id: true }).map(item => item._max.id)
          },
          pledgeStatus: { equals: filter.pledge, mode: 'insensitive' },
          pledgeType: 'BlockAgent'
        }
      }
    };
  }
}

也可以用Raw查询更精准控制：

if(filter.pledge) {
  where.voter = {
    ...where.voter,
    id: {
      in: this.prisma.$queryRaw`
        SELECT "v"."id"
        FROM "Voter" AS "v"
        JOIN (
          SELECT "voterId", MAX("id") AS "maxId"
          FROM "VoterPledge"
          WHERE "pledgeType" = 'BlockAgent'
          GROUP BY "voterId"
        ) AS "vp_max" ON "v"."id" = "vp_max"."voterId"
        JOIN "VoterPledge" AS "vp" ON "vp"."id" = "vp_max"."maxId"
        WHERE "vp"."pledgeStatus" = ${filter.pledge}
      `
    }
  };
}

最终效果 #

修复后的代码既保留了多筛选条件的叠加效果，又能准确匹配每个选民最新的BlockAgent承诺状态，符合需求。

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