TypeORM多对多关联如何查询包含权限匹配及无关联权限的Ticket记录

错误原因 #

1. 直接写ticketPermission IS NULL属于语法错误，不能直接对关联表别名做非空判断，需要判断关联表的主键字段ticketPermission.id IS NULL。
2. 仅替换为ticketPermission.id IS NULL仍无效果，通常有两个诱因：
   • 当myPermissions为空数组时，IN (:...myPermissions)会生成IN ()的非法SQL语法，直接触发报错
   • 多对多左连接会生成多行数据，若工单关联了不在你权限列表内的其他权限，这些行的ticketPermission.id不为空也不在权限列表内，会被正常过滤，符合需求逻辑

正确实现代码 #

const baseQuery = this.ticketRepository
  .createQueryBuilder('ticket')
  .leftJoinAndSelect('ticket.permissions', 'ticketPermission');

// 处理权限数组为空的边界情况
if (myPermissions.length > 0) {
  baseQuery.where(
    'ticketPermission.id IN (:...myPermissions) OR ticketPermission.id IS NULL',
    { myPermissions }
  );
} else {
  // 无权限时仅返回没有关联任何权限的工单
  baseQuery.where('ticketPermission.id IS NULL');
}

// 若查询返回重复工单可添加.distinct()去重
const openTickets = await baseQuery.getMany();

可选优化方案（性能更高） #

如果不需要在返回结果中带出关联的权限列表，可改用子查询实现，避免左连接生成大量冗余行：

const openTickets = await this.ticketRepository
  .createQueryBuilder('ticket')
  .where(qb => {
    const hasPermissionSubQuery = qb.subQuery()
      .select('1')
      .from('ticket_permissions_permission', 'tpp') // 替换为你的多对多中间表实际名称
      .where('tpp."ticketId" = ticket.id')
      .andWhere('tpp."permissionId" IN (:...myPermissions)', { myPermissions })
      .getQuery();

    const noPermissionSubQuery = qb.subQuery()
      .select('1')
      .from('ticket_permissions_permission', 'tpp')
      .where('tpp."ticketId" = ticket.id')
      .getQuery();

    return `EXISTS ${hasPermissionSubQuery} OR NOT EXISTS ${noPermissionSubQuery}`;
  })
  .setParameter('myPermissions', myPermissions)
  .getMany();

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