Search code examples
sqlmany-to-manytypeorm

TypeORM: edit a many-to-many relations with custom properties

How can I edit a entity with many-to-many relations with custom properties? Typeorm docs examples are very limited for this type of relationship: https://typeorm.io/#/many-to-many-relations/many-to-many-relations-with-custom-properties

I have 3 tables: users, roles, user_roles

User.ts

@Entity('users')
class User {
  @PrimaryGeneratedColumn('increment')
  id: number;

  @Column()
  name: string;

  @Column()
  email: string;
  
  @OneToMany(() => UserRole, userRole => userRole.user, {
    cascade: true,
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  @JoinColumn({ referencedColumnName: 'user_id' })
  userRoles!: UserRole[];
}

export default User;

Role.ts

@Entity('roles')
class Role {
  @PrimaryGeneratedColumn('increment')
  id: number;

  @Column()
  role: string;

  @OneToMany(() => UserRole, userRole => userRole.role, {
    cascade: true,
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  })
  @JoinColumn({ referencedColumnName: 'role_id' })
  userRoles!: UserRole[];
}

export default Role;

UserRole.ts

@Entity('user_roles')
class UserRole {
 @PrimaryGeneratedColumn('increment')
 id: number;

 @Column()
 user_id!: number;

 @Column()
 role_id!: number;
 
 @CreateDateColumn()
 created_at: Date;

 @UpdateDateColumn()
 updated_at: Date;

 @ManyToOne(() => User, user => user.userRoles)
 @JoinColumn({ name: 'user_id' })
 user!: User;

 @ManyToOne(() => Role, role => role.userRoles)
 @JoinColumn({ name: 'role_id' })
 role!: Role;
}

export default UserRole;

Create a user with related userRoles is working:

const user = {
  name: 'Test user',
  email: '[email protected]',
  userRoles: [
    { role_id: 1 },
    { role_id: 2 }, 
    { role_id: 3 },
    { role_id: 4 }
  ],
};

await this.usersRepository.save(user);

Update its not working, I want to replace all related user_roles with the new ones, is there any save strategy option in typeorm many to many relation? I have seen in other orms for example ‘replace’ or ‘append‘ option in relation definition This is how im trying to update:

// load user
const user = await this.usersRepository.findById(user_id);

/*
returns:

User {
 id: 2,
 name: 'Test user',
 email: '[email protected]',
 userRoles: [
   UserRole {
     id: 376,
     user_id: 2,
     role_id: 1,
     created_at: 2020-09-18T17:26:52.000Z,
     updated_at: 2020-09-18T17:26:52.000Z
   },
   UserRole {
     id: 377,
     user_id: 2,
     role_id: 2,
     created_at: 2020-09-18T17:26:52.000Z,
     updated_at: 2020-09-18T17:26:52.000Z
   },
   UserRole {
     id: 378,
     user_id: 2,
     role_id: 3,
     created_at: 2020-09-18T17:26:53.000Z,
     updated_at: 2020-09-18T17:26:53.000Z
   },
   UserRole {
     id: 379,
     user_id: 2,
     role_id: 4,
     created_at: 2020-09-18T17:26:53.000Z,
     updated_at: 2020-09-18T17:26:53.000Z
   }
 ]
}
*/

// edit user
const newRolesIds = [4, 5, 6, 7];

user.name = 'updated name';
user.email = '[email protected]';


// isn`t it suposed to replace old user_roles and create this new ones?
user.userRoles = newRolesIds.map(roleId => {
 // keep the roles that are already saved in database and are in the newRolesIds 
     const existingRole = user.userRoles.find(userRole => userRole.role_id === roleId);

     if (existingRole) {
       return existingRole;
     }

     // add new roles
     const userRole = new UserRole();
     userRole.user_id = user.id;
     userRole.role_id = roleId;
     return userRole;
});

/*
user after edit:
User {
 id: 2,
 name: 'updated name',
 email: '[email protected]',
 userRoles: [
   UserRole { user_id: 2, role_id: 4 },
   UserRole { user_id: 2, role_id: 5 },
   UserRole { user_id: 2, role_id: 6 },
 ]
}
*/

this.ormRepository.save(user);

Save fails with this sql error: Its a strange update sql, not recognizing user_id and role_id is not even present in sql, am I forgetting to define something in relationships?

QueryFailedError: Cannot add or update a child row: a foreign key constraint fails (`user_roles`, CONSTRAINT `UserRoleUserIdFK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
  code: 'ER_NO_REFERENCED_ROW_2',
  errno: 1452,
  sqlState: '23000',
  sqlMessage: 'Cannot add or update a child row: a foreign key constraint fails (`user_roles`, CONSTRAINT `UserRoleUserIdFK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)',
  query: 'UPDATE `user_roles` SET `user_id` = ?, `updated_at` = CURRENT_TIMESTAMP WHERE `id` = ?',
  parameters: [ undefined, 376 ]

sql log shows insert for new roles 5, 6, 7 and after that its trying to update the roles 1,2,3 that should be deleted throwing a sql error

according to the docs it is only necessary remove from the array to delete or should i do this delete manually? https://typeorm.io/#/many-to-many-relations/deleting-many-to-many-relations

query: SELECT `user_roles`.`id` AS `id`, `user_roles`.`user_id` AS `user_id` FROM `user_roles` `user_roles` WHERE ((`user_roles`.`user_id` = ?)) -- PARAMETERS: [2]
query: START TRANSACTION
query: INSERT INTO `user_roles`(`id`, `user_id`, `role_id`, `created_at`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: [2,5]
query: SELECT `UserRole`.`id` AS `UserRole_id`, `UserRole`.`created_at` AS `UserRole_created_at` FROM `user_roles` `UserRole` WHERE `UserRole`.`id` = ? -- PARAMETERS: [7]
query: INSERT INTO `user_roles`(`id`, `user_id`, `role_id`, `created_at`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: [2,6]
query: SELECT `UserRole`.`id` AS `UserRole_id`, `UserRole`.`created_at` AS `UserRole_created_at` FROM `user_roles` `UserRole` WHERE `UserRole`.`id` = ? -- PARAMETERS: [8]
query: INSERT INTO `user_roles`(`id`, `user_id`, `role_id`, `created_at`) VALUES (DEFAULT, ?, ?, DEFAULT) -- PARAMETERS: [2,7]
query: SELECT `UserRole`.`id` AS `UserRole_id`, `UserRole`.`created_at` AS `UserRole_created_at` FROM `user_roles` `UserRole` WHERE `UserRole`.`id` = ? -- PARAMETERS: [9]
query: UPDATE `user_roles` SET `user_id` = ? WHERE `id` = ? -- PARAMETERS: [null,1]
query: UPDATE `user_roles` SET `user_id` = ? WHERE `id` = ? -- PARAMETERS: [null,2]
query: UPDATE `user_roles` SET `user_id` = ? WHERE `id` = ? -- PARAMETERS: [null,3]

query failed: UPDATE `user_roles` SET `user_id` = ? WHERE `id` = ? -- PARAMETERS: [null,1]
Solution

  • I figuredout that typeorm currently don't support updates this way. The default behavior is set the related records keys to null instead of delete it and in my case this behavior dont work because the related have a FK with index. There are a PR to add this feature to delete related records instead of set null: https://github.com/typeorm/typeorm/pull/7105

    The workaround is delete manually the unused records before update