I have a nestJS project with two entities, a user entity and a chatroom entity, there can be multiple users in a chatroom and users can be in multiple chatrooms, here's the chatroom entity :

@Entity()
export class ChatRoom{
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @Column('int', {array: true, default: []})
    public adminId: number[];

    @Column()
    ownerId: number;

    @ManyToMany(() => User, { cascade: true, onUpdate:'CASCADE' })
    @JoinTable()
    users: User[];
}

and user entity :

@Entity()
export class User {
  @PrimaryColumn()
  public id: number;
 
  @Column({nullable: true})
  public username: string;

  @Column()
  public log: string;

  @Column({default: 0})
  public rank: number;

  @Column('int', {array: true, default: []})
  public blocked: number[];

  @ManyToMany(() => ChatRoom, room => room.users)
  rooms: ChatRoom[];
}

Right now with querybuilder I managed to get the list of users in a chatroom and to get the list of chatrooms a user has joined, however I would like to know how to delete a single relationship, if a user wants to leave a room, how do i remove the matching row without removing the user entity or the chatroom entity itself ?

This is the initial chatroom service code I wrote in order to remove a specific user from a chatroom's users list :

async removeUserFromRoom(user: User, roomid: number)
    {
        const room = await this.chatRepo.findOne(roomid);
        if (room)
        {
            room.users = room.users.filter(user => {
                user.id !== user.id
            })
            await this.chatRepo.save(room);
        }
        else
            throw new HttpException('Room not found', HttpStatus.NOT_FOUND);
    }

Although this solution will not work because the room entity does not have an array of users defined, do I need to run a query in order to remove the user from the chatroom ? or is there a way to handle this in TypeORM ? if it seems like I misunderstood something please let me know as I am relatively new to databases and queries.

CodePudding user response：

Your room entity does not have a users array because you don't query the relation when using the repository.

const room = await this.chatRepo.findOne(roomid, { relations: ['users'] }); should fix this.

CodePudding user response：

According to the documentation, it seems that deletion is only possible from the 'owning' side.

Depending on your needs, you can:

• handle the intermediate table yourself
• reverse the operation

With the current model, you need to remove the room from the user. (Yes, this is grammatically incorrect, but it illustrates what you have to do.)

So you are to search for the user in the room, not the users' rooms.

const user = await this.userRepo.findOne(userid); 

But... You have user, so:

    async removeUserFromRoom(user: User, roomid: number) 
    {
        //TODO: refresh user data
        user.rooms.filter(room => {
            room.id != roomid
        })
        await this.userRepo.save(user);
    }

Important thing

You need to analyze use cases as you may find it more efficient to move @JoinTable() to the User entity.

But wait ...

You messed up that you have no defined users in the room. So:

• you have them defined (they could have been unloaded. It's easy to check, just use console.log(await room.users))

• you have a bug in your code: you define the user variable twice. In the main function and again in the filter. Finally, the condition user.id != user.id is always true because you compare the user from the filter.

BTW:

For performance reasons, I would move the 'owning' side to 'User' due to the potential amount of related records. (Typically there are more users per channel than channels pre user)