Question 203357 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to automatically update and get a relation column based on other relation

I am trying to connect 3 relations – Photo, PhotoComment and PhotoRating. I want to let my users to have a possibility to rate a photo. These photos can have multiple comments by many users and multiple rates by many users (but only 1 rate per specific user). And I want to show that user rate on all comments that belongs to photos. However, I cannot make things work to show a rate inside comment and update it automatically whenever user change photo rate (uses ratePhoto method)

When I call ratePhoto method I can see in PGAdmin that photoRatingId is not connected with PhotoComment and it’s not updating rate there. However, ratePhoto method works fine and saves rate of Photo correctly

I’m using NestJS, TypeORM, PostgreSQL and RxJS. I highly prefer to work on repository instead of 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;
}

What am I doing wrong?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Okay, I figured it out. Most of the things were made correctly however because I wasn't updating PhotoComment Entity, my rate wasn't present on PhotoRating Entity. I didn't want to get all the comments and save it manually, however I forgot about .update() method. I also refactored my method to use transaction and instead of RxJS I switched to promises because unfortunately, RxJS here wasn't the best option. So my code now looks more or less like this:

    photos.service.ts

    public async ratePhoto(photoId: number, ratePhotoDto: RatePhotoDto, user: User): Promise<void> {
        await this.entityManager.transaction(async (entityManager: EntityManager) => {
            const photo: Photo = await this.photoRepository.findOne({ where: { photo: { id: photoId } } });
            const photoRating: PhotoRating = await this.photoRatingRepository.findOne({ where: { photo: { id: photo.id }, user: { id: user.id } } });
    
            if (!photoRating) {
                const newRating: PhotoRating = await entityManager.save( // Saving new rate of photo
                    PhotoRating, 
                    { ...ratePhotoDto, user, photo }
                );
    
                await entityManager.update( // Fast update all comments that should be updated
                    PhotoComment,
                    { addedBy: { id: user.id }, photo: { id: photo.id } },
                    { photoRating: newRating }
                );
    
                return;
            }
    
            await entityManager.save( // Saving rate of photo
                PhotoRating, 
                { ...photoRating, ...ratePhotoDto }
            );
    
            await entityManager.update( // Fast update all comments that should be updated
                PhotoComment,
                { addedBy: { id: user.id }, photo: { id: photo.id } },
                { photoRating }
            );
        });
    }

    photo.entity.ts

    @Entity()
export class Photo { // No changes here
    //...

    @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 { // Deleted JoinColumn because I don't need that
    //...

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

    @ManyToOne(() => PhotoRating, (photoRating: PhotoRating) => photoRating.photoComment)
    public photoRating: PhotoRating;
}

    photo-rating.entity.ts

    @Entity()
@Unique(['user', 'photo'])
export class PhotoRating { // Deleted { cascade: true | from PhotoComment because I don't want it
    //...
    
    @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)
    public photoComment: PhotoComment;
}

    And now it's working perfectly and fast. I had also to adjust my code when user adds a comment when photo is already rated but that's the same case with .update() method like above


  2. 0

    In my opinion, the problem is that you have not set the photoComment

    property on the photoRating entity. To do this, add the following code to the ratePhoto method

    photoRating.photoComment = photoComment;

    This will ensure that the photoComment property of the photoRating entity is set correctly.

    The method should be as follows

    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;
        photoRating.photoComment = photoComment;
        return this.photoRatingRepository.save(photoRating);
      }),
    )),
  );
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search