NestJS+TypeORM更新多对多关联时出现Cannot query across many-to-many报错

在基于NestJS与PostgreSQL的项目中，使用TypeORM更新Student实体时触发错误：Cannot query across many-to-many for property subject。创建实体时逻辑正常，但更新操作失败。

相关代码与报错信息 #

Student实体代码 #

import { Entity, Column, PrimaryGeneratedColumn, ManyToOne, ManyToMany, UpdateDateColumn, CreateDateColumn, JoinTable } from "typeorm";
//Orm Entity
import { User } from "src/user/model/user.entity";
import { Class } from "src/classes/model/class.entity";
import { Subject } from "src/subject/model/subject.entity";
@Entity()
export class Student {
    @PrimaryGeneratedColumn("uuid")
    id: string;
    @ManyToOne(() => Class)
    class: Class;
    @ManyToMany(() => Subject, { cascade: true })
    @JoinTable()
    subject: Subject[];
    @Column({ type: "text" })
    studentId: string;
    @Column({ type: "text" })
    name: string;
    @Column({ type: "text", nullable: true })
    image: string;
    @Column({ type: "text" })
    gender: string;
    @Column({ type: "text" })
    dob: string;
    @Column({ type: "text", nullable: true })
    blood: string;
    @Column({ type: "text", nullable: true })
    religion: string;
    @Column({ type: "text", nullable: true })
    number: string;
    @Column({ type: "text", nullable: true })
    email: string;
    @Column({ type: "text" })
    date: string;
    @Column({ type: "text", nullable: true })
    birthCertificate: string;
    @Column({ type: "text" })
    fatherName: string;
    @Column({ type: "text", nullable: true })
    fatherNidNumber: string;
    @Column({ type: "text", nullable: true })
    fatherPhone: string;
    @Column({ type: "text" })
    motherName: string;
    @Column({ type: "text", nullable: true })
    motherNidNumber: string;
    @Column({ type: "text", nullable: true })
    motherPhone: string;
    @Column({ type: "text", nullable: true })
    guardianName: string;
    @Column({ type: "text", nullable: true })
    guardianNidNumber: string;
    @Column({ type: "text", nullable: true })
    guardianPhone: string;
    @Column({ type: "text" })
    address: string;
    @Column({ type: "text" })
    school: string;
    @ManyToOne(() => User)
    createdBy: User;
    @UpdateDateColumn({ type: "timestamptz" })
    updated_at: Date;
    @CreateDateColumn({ type: "timestamptz" })
    created_at: Date;
}

更新方法代码 #

async update(studentInput: StudentInput, id: string, reqUser: ReqUser) {
        const subjects: Subject[] = await Promise.all(
            studentInput.subject.map((id: string) => this.subjectRepository.findOneBy({ id: id }))
        );
        const result = await this.studentRepository.update(id, {
            ...studentInput,
            class: { id: studentInput.class },
            createdBy: { id: reqUser.id },
            subject: subjects
        });
        if (result.affected === 0) throw new NotFoundException("Student not found!");
        return {
            success: true,
            message: "Student updated successfully!"
        }
    };

报错栈 #

[Nest] 13464  - 03/17/2023, 6:41:36 PM   ERROR [ExceptionsHandler] Cannot query across many-to-many for property subject
Error: Cannot query across many-to-many for property subject
    at UpdateQueryBuilder.createPropertyPath (G:\Web Devlopment\E-Campus\server\src\query-builder\QueryBuilder.ts:1367:27)
    at UpdateQueryBuilder.createUpdateExpression (G:\Web Devlopment\E-Campus\server\src\query-builder\UpdateQueryBuilder.ts:493:18)
    at UpdateQueryBuilder.getQuery (G:\Web Devlopment\E-Campus\server\src\query-builder\UpdateQueryBuilder.ts:53:21)
    at UpdateQueryBuilder.getQueryAndParameters (G:\Web Devlopment\E-Campus\server\src\query-builder\QueryBuilder.ts:507:28)
    at UpdateQueryBuilder.execute (G:\Web Devlopment\E-Campus\server\src\query-builder\UpdateQueryBuilder.ts:142:50)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at StudentService.update (G:\Web Devlopment\E-Campus\server\src\student\student.service.ts:107:24)
    at target (G:\Web Devlopment\E-Campus\server\node_modules\@nestjs\core\helpers\external-context-creator.js:74:28)
    at Object.updateStudent (G:\Web Devlopment\E-Campus\server\node_modules\@nestjs\core\helpers\external-proxy.js:9:24)

问题原因 #

TypeORM的Repository.update()方法直接生成SQL的UPDATE语句，仅能更新实体自身字段，无法处理多对多关联关系的变更——因为多对多关联需要操作@JoinTable()生成的中间表，而update()方法不支持自动维护这类关联表的操作。创建时能正常运行是因为save()方法会自动处理关联关系的级联操作，而update()不具备该能力。

解决方案 #

方法一：查询实体后修改并保存（推荐） #

先通过ID查询出Student实体，修改其属性和关联关系后调用save()方法，利用TypeORM的级联特性自动更新中间表。

修改后的更新方法代码：

async update(studentInput: StudentInput, id: string, reqUser: ReqUser) {
    // 查询要更新的Student实体，同时加载subject关联
    const student = await this.studentRepository.findOne({
        where: { id },
        relations: ['subject']
    });

    if (!student) throw new NotFoundException("Student not found!");

    // 查询关联的Subject实体
    const subjects: Subject[] = await Promise.all(
        studentInput.subject.map((subjectId: string) => this.subjectRepository.findOneBy({ id: subjectId }))
    );

    // 更新实体属性
    student.studentId = studentInput.studentId;
    student.name = studentInput.name;
    student.image = studentInput.image;
    student.gender = studentInput.gender;
    student.dob = studentInput.dob;
    student.blood = studentInput.blood;
    student.religion = studentInput.religion;
    student.number = studentInput.number;
    student.email = studentInput.email;
    student.date = studentInput.date;
    student.birthCertificate = studentInput.birthCertificate;
    student.fatherName = studentInput.fatherName;
    student.fatherNidNumber = studentInput.fatherNidNumber;
    student.fatherPhone = studentInput.fatherPhone;
    student.motherName = studentInput.motherName;
    student.motherNidNumber = studentInput.motherNidNumber;
    student.motherPhone = studentInput.motherPhone;
    student.guardianName = studentInput.guardianName;
    student.guardianNidNumber = studentInput.guardianNidNumber;
    student.guardianPhone = studentInput.guardianPhone;
    student.address = studentInput.address;
    student.school = studentInput.school;
    // 更新多对一关联
    student.class = { id: studentInput.class } as Class;
    student.createdBy = { id: reqUser.id } as User;
    // 更新多对多关联
    student.subject = subjects;

    // 保存更新后的实体，级联处理多对多中间表
    await this.studentRepository.save(student);

    return {
        success: true,
        message: "Student updated successfully!"
    };
};

方法二：使用QueryBuilder手动操作中间表 #

如果不想加载整个实体，可以先更新Student自身字段，再通过QueryBuilder手动删除旧的关联、插入新的关联：

async update(studentInput: StudentInput, id: string, reqUser: ReqUser) {
    // 先更新Student自身字段
    const updateResult = await this.studentRepository.update(id, {
        ...studentInput,
        class: { id: studentInput.class },
        createdBy: { id: reqUser.id }
    });

    if (updateResult.affected === 0) throw new NotFoundException("Student not found!");

    // 获取中间表名称（TypeORM默认格式为：student_subject_subject）
    const joinTableName = 'student_subject_subject';
    // 删除旧的关联
    await this.studentRepository
        .createQueryBuilder()
        .delete()
        .from(joinTableName)
        .where('studentId = :studentId', { studentId: id })
        .execute();

    // 插入新的关联
    if (studentInput.subject.length > 0) {
        const subjectRelations = studentInput.subject.map(subjectId => ({
            studentId: id,
            subjectId: subjectId
        }));
        await this.studentRepository
            .createQueryBuilder()
            .insert()
            .into(joinTableName)
            .values(subjectRelations)
            .execute();
    }

    return {
        success: true,
        message: "Student updated successfully!"
    };
};

注意：中间表名称需要根据实际情况调整，可通过TypeORM的日志功能查看生成的表名，或者在@JoinTable()中显式指定名称：

@ManyToMany(() => Subject, { cascade: true })
@JoinTable({ name: 'student_subject' }) // 自定义中间表名
subject: Subject[];

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