CODEWITHSUNDEEP
typescriptormtypeorm
Markus
Markus

Reputation: 2061

TypeORM Many-to-Many join table extra column

I've a simple many to many relationship with TypeORM

User Entity

@Entity({ name: 'users' })
export class User {
  @PrimaryColumn()
  id: string;

  @Column()
  email: string;

  @Column()
  password: string;

  @ManyToMany((type) => Organization, (organization) => organization.users)
  @JoinTable({
    name: 'user_organizations',
    joinColumn: {
      name: 'user_id',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'organization_id',
      referencedColumnName: 'id',
    },
  })
  organizations: Organization[];

Organization Entity

@Entity({ name: 'organizations' })
export class Organization {
  @PrimaryColumn()
  id: string;

  @Column()
  name: string;

  @ManyToMany((type) => User, (user) => user.organizations)
  users: User[];

}

My goal is to create a relationship which not only defines which user relates to which organisation it should also contains information in which role the user is related to a organisation. My idea was to extend the relation table with an extra role column for that.

create table user_organizations(
  user_id varchar(64) not null,
  organization_id varchar(64) not null,
  role varchar(64) not null,
  foreign key (user_id) references users(id),
  foreign key (organization_id) references organizations(id),
);

My question is how to store the role in the database. Currently I'm doing something like this.

let user = new User();
let organization = new Organization();
organization.name = name;
organization.users = [user];
organization = await this.organizationRepository.save(organization);

How can I fill also the role column via TypeORM?

Upvotes: 3

Views: 3231

Answers (1)

Jiri Kralovec
Jiri Kralovec

Reputation: 1617

The best approach to this kind of problem is to create a separate table solely for Role and then refer to this table in the user_organizations.

But think of this scenario - what if a user doesn't have just one role? It can and does happen. That being said, my suggestion would be handling the role outside of the user_organisations table. Since the table is M2M, the primary key will be a composite of User.ID and Organisation.ID. My suggestion would be:

  1. Create separate table for all roles (like Users or Organisations)
  2. Create M2M table between Roles and UserOrganisations

Upvotes: 3

Related Questions