Many to Many Joins in TypeORM

Question

We are currently working on implementing NestJS against our DB. We decided to use TypeORM to give us a strong ORM to use for most of our basic to intermediate queries. However, I am having issues setting up one particular relationship in our entities.

Our table structure comes from an existing system that cannot be modified. It is as follows:

Employee Table

+-----------+-----------+---------------+
| HRCO (PK) | HRRef(PK) | udDisplayName |
+-----------+-----------+---------------+
|         2 |       323 | John          |
|         2 |       500 | Larry         |
|         1 |        29 | Jim           |
+-----------+-----------+---------------+

**Skill Table**
+----------+----------+----------------+
| HRCo(PK) | Code(PK) |  Description   |
+----------+----------+----------------+
|        2 | CODE1    | Test Class     |
|        2 | CODE2    | Crane Training |
|        1 | CODE1    | Truck Training |
+----------+----------+----------------+

**Join Table - EmployeeSkills**
+-----------+---------------+-----------+------------+
| HRCO (FK) | Employee (FK) | Code (FK) |  Expires   |
+-----------+---------------+-----------+------------+
|         2 |           323 | CODE1     | 2019-01-01 |
|         2 |           323 | CODE2     | 2020-01-01 |
|         1 |            29 | CODE1     | 2020-01-01 |
+-----------+---------------+-----------+------------+

I recognize this is a many-to-many relationship that has a composite foreign key. Most of the NestJS docs lead you down using the ManyToMany and OneToMany x 2 pattern of establishing the relationship. However, those seem to only work if the joiner table has one key for each table rather than a composite.

Right now my classes look like the following:

Skills

import { Entity, Column, PrimaryColumn, ManyToOne, OneToMany } from "typeorm";
import { EmployeeToSkillEntity } from "../employee-to-skill/employee-skill.entity";

@Entity({name:"skills"})
export class SkillEntity {

  @PrimaryColumn({ name: "HRCo" })
  company: number;

  @PrimaryColumn({ name: "Code" })
  code: number;

 

  @Column({name:"Description"})
  description: Date;

  @OneToMany(type => EmployeeToSkillEntity, employeeToSkill => employeeToSkill.code)
  employeeToSkills: EmployeeToSkillEntity[]

}

Employee

import { Entity, Column, PrimaryColumn, OneToMany } from "typeorm";
import { EmployeeToSkillEntity } from "../employee-to-skill/employee-skill.entity";

/**
 * @ignore
 */
@Entity({name:"employee"})
export class EmployeeEntity {
  @PrimaryColumn({ name: "HRCo" })
  company: number;

  @PrimaryColumn({ name: "HRRef" })
  employeeNumber: number;

  @Column({name: "udDisplayName"})
  displayName: string;

  @OneToMany(type => EmployeeToSkillEntity, employeeToSkill => employeeToSkill.employee)
  employeeToSkills: EmployeeToSkillEntity[]

}

import { Entity, Column, PrimaryColumn, ManyToOne, PrimaryGeneratedColumn } from "typeorm";
import { EmployeeEntity } from "../employee/employee.entity";
import { SkillEntity } from "../skill/skill.entity";

/**
 * @ignore
 */
@Entity({ name: "employeeskills" })
export class EmployeeToSkillEntity {
  @PrimaryColumn({ name: "HRCo" })
  companyNumber: number;

  @PrimaryColumn({ name: "HRRef" })
  employeeNumber: number;

  @PrimaryColumn({ name: "Code" })
  code: string;

  @Column({ name: "CertDate" })
  certDate: Date;

  @Column({ name: "ExpireDate" })
  expireDate: Date;

  @Column({ name: "SkillTester" })
  skillTester: string;

  @Column({ name: "HistSeq" })
  histSeq: string;

  @Column({ name: "Notes" })
  notes: string;

  @Column({ name: "UniqueAttchID" })
  attachmentID: number;

  @Column({ name: "Type" })
  type: string;

  @Column({ name: "KeyID" })
  keyID: number;

  @Column({ name: "udLastModDate" })
  lastModifiedDate: Date;

  @Column({ name: "udLicense" })
  license: number;

  @ManyToOne(type => EmployeeEntity, (employee) => employee.employeeToSkills)
  @JoinColumn([{ name: "HRCo", referencedColumnName: "companyNumber" }, { name: "HRRef", referencedColumnName: "employeeNumber" }])
  employee: EmployeeEntity;

  @ManyToOne(type => SkillEntity, (skill) => skill.employeeToSkills)
  @JoinColumn([{ name: "HRCo", referencedColumnName: "companyNumber" }, { name: "Code", referencedColumnName: "code" }])
  skill: SkillEntity;
}

However, I am getting an error on query generation because I am getting columns for the relationship + the property name like "skillCode".

Any help would be appreciated.

Thanks

Solution

In order to properly 'configure' TypeORM used by Nest.js, consider @JoinTable which supports composite keys as well. Please give it a try (hopefully, judging from your questions, it is the issue with the query itself and properly getting through join table - and the @JoinTable may already help you with that) and it still getting issues, please update the code with exact query and error you are getting. Any logs from TypeORM's debug mode would be nice as well.

Cheers!

Comments

[Jeremy Keczan]

Thanks for the reply. We just got it figured out this morning. We ended up using JoinColumn to override the joining columns and set them up correctly. I have updated the example to show how we ended up solving the issue. See the JoinColumn() decorator for the Many to One relationships in the EmployeeToSkill class