如何自动更新并基于关联获取PhotoComment中的用户评分字段？

我尝试关联Photo、PhotoComment和PhotoRating三个实体，需求如下：

• 用户可对照片评分，单个用户对单张照片仅能评一次
• 照片可拥有多条用户评论及多个用户评分
• 在该照片的所有评论中显示当前用户的评分
• 调用ratePhoto方法修改评分时，自动更新评论中的评分

目前遇到的问题：调用ratePhoto方法后，PGAdmin中可见PhotoComment未关联photoRatingId，评论中的评分也未更新，但ratePhoto能正确保存照片的评分。技术栈为NestJS、TypeORM、PostgreSQL、RxJS，优先使用Repository而非QueryBuilder。相关代码如下：

photos.service.ts #

public ratePhoto(photoId: number, ratePhotoDto: RatePhotoDto, user: User): Observable<any> {
        return this.getPhoto(photoId).pipe(
            switchMap((photo: Photo) => from(this.photoRatingRepository.findOneBy({ photo: { id: photo.id }, user: { id: user.id } })).pipe(
                map((photoRating: PhotoRating) => {
                    if (!photoRating) {
                        const newRating: PhotoRating = new PhotoRating();

                        newRating.rate = ratePhotoDto.rate;
                        newRating.user = user;
                        newRating.photo = photo;

                        return this.photoRatingRepository.save(newRating);
                    }

                    photoRating.rate = ratePhotoDto.rate;

                    return this.photoRatingRepository.save(photoRating);
                }),
            )),
        );
    }

photo.entity.ts #

@Entity()
export class Photo {
    //...

    @OneToMany(() => PhotoComment, (photoComment: PhotoComment) => photoComment.photo)
    public photoComments: PhotoComment[];

    @OneToMany(() => PhotoRating, (photoRating: PhotoRating) => photoRating.photo)
    public photoRatings: PhotoRating[];
}

photo-comment.entity.ts #

@Entity()
export class PhotoComment {
    //...

    @ManyToOne(() => Photo, (photo: Photo) => photo.photoComments, { onDelete: 'CASCADE' })
    public photo: Photo;

    @ManyToOne(() => PhotoRating, (photoRating: PhotoRating) => photoRating.photoComment)
    @JoinColumn({ name: 'rate' })
    public photoRating: PhotoRating;
}

photo-rating.entity.ts #

@Entity()
@Unique(['user', 'photo'])
export class PhotoRating {
    //...
    
    @Transform(({ value }) => +value)
    @Column({ type: 'decimal', precision: 3, scale: 2, default: 0 })
    public rate: number;

    @ManyToOne(() => Photo, (photo: Photo) => photo.photoRatings, { onDelete: 'CASCADE' })
    public photo: Photo;

    @OneToMany(() => PhotoComment, (photoComment: PhotoComment) => photoComment.photoRating, { cascade: true })
    public photoComment: PhotoComment;
}

1. 实体关联逻辑错误 #

• 外键列设置错误：PhotoComment中@JoinColumn({ name: 'rate' })把外键列指定为rate，但rate是PhotoRating的业务字段，而非主键。外键应关联PhotoRating的主键，正确列名应为photo_rating_id。
• 关联字段命名错误：PhotoRating中的photoComment字段是@OneToMany关联，对应多个评论，应命名为复数photoComments。
• 缺少用户关联：PhotoComment未关联User，无法定位当前用户对该照片的所有评论，导致无法批量更新关联关系。

2. 未维护评论与评分的关联关系 #

当前ratePhoto方法仅处理了PhotoRating的保存，未同步更新对应PhotoComment的photoRating关联，这是评论未关联评分的核心原因。

修正后的代码 #

photo-comment.entity.ts #

@Entity()
export class PhotoComment {
    //...

    @ManyToOne(() => Photo, (photo: Photo) => photo.photoComments, { onDelete: 'CASCADE' })
    public photo: Photo;

    // 新增：每个评论归属一个用户
    @ManyToOne(() => User, { onDelete: 'CASCADE' })
    public user: User;

    @ManyToOne(() => PhotoRating, (photoRating: PhotoRating) => photoRating.photoComments)
    // 修正外键列名，关联PhotoRating的主键
    @JoinColumn({ name: 'photo_rating_id' })
    public photoRating: PhotoRating;
}

photo-rating.entity.ts #

@Entity()
@Unique(['user', 'photo'])
export class PhotoRating {
    //...
    
    @Transform(({ value }) => +value)
    @Column({ type: 'decimal', precision: 3, scale: 2, default: 0 })
    public rate: number;

    @ManyToOne(() => Photo, (photo: Photo) => photo.photoRatings, { onDelete: 'CASCADE' })
    public photo: Photo;

    // 新增：每个评分归属一个用户
    @ManyToOne(() => User, { onDelete: 'CASCADE' })
    public user: User;

    // 修正字段名为复数，对应多条评论
    @OneToMany(() => PhotoComment, (photoComment: PhotoComment) => photoComment.photoRating, { cascade: true })
    public photoComments: PhotoComment[];
}

photos.service.ts（ratePhoto方法） #

public ratePhoto(photoId: number, ratePhotoDto: RatePhotoDto, user: User): Observable<any> {
    return this.getPhoto(photoId).pipe(
        switchMap((photo: Photo) => from(this.photoRatingRepository.findOneBy({ photo: { id: photo.id }, user: { id: user.id } })).pipe(
            switchMap((photoRating: PhotoRating) => {
                // 处理评分的新增或更新
                const rating$ = !photoRating 
                    ? from(this.photoRatingRepository.save({
                        rate: ratePhotoDto.rate,
                        user,
                        photo
                      }))
                    : from(this.photoRatingRepository.save({
                        ...photoRating,
                        rate: ratePhotoDto.rate
                      }));

                // 保存评分后，更新当前用户对该照片的所有评论的评分关联
                return rating$.pipe(
                    switchMap((savedRating) => from(this.photoCommentRepository.findBy({ 
                        photo: { id: photoId }, 
                        user: { id: user.id } 
                    })),
                    switchMap((comments) => {
                        const updatedComments = comments.map(comment => ({
                            ...comment,
                            photoRating: savedRating
                        }));
                        return from(this.photoCommentRepository.save(updatedComments));
                    }),
                    map(() => ({ success: true, message: '评分及关联评论更新完成' }))
                );
            })
        )
    );
}

• 新增PhotoComment与User的关联，确保能精准定位当前用户对目标照片的所有评论
• 修正外键列名与关联字段命名，符合TypeORM关联规范
• 在评分保存后，批量更新对应评论的photoRating关联，确保评论能同步显示最新评分
• cascade: true配置可确保PhotoRating变更时，关联评论自动同步，但手动更新关联关系更可控、直观

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