Nested SELECT with LEFT JOIN - TypeORM, NestJS and PostgreSQL

Question

I am currently working on one feature that should implement Many-To-Many relationship with custom properties using NestJS and TypeORM.

Tech used

NestJS
PostgreSQL
TypeORM

Tables info

Skill Table

id	Name
1	.NET
2	Azure
3	JS

Resume Table

id	Name	slug
1	resume-1	resume-1
2	resume-2	resume-2
3	resume-3	resume-3

Pivot Table

id	resumeId	skillId	level
1	1	1	Advanced
2	1	2	Advanced
3	2	3	Advanced

Expected result

Return all resumes even if no skills are present
Filter query if search params are passed

The challenge

The second point from expected results is pretty much clear. But, the first point is very difficult to handle with TypeORM.

Since it is a Many-To-Many one would expect to start with the Pivot Table and right join the two tables. That will result in getting all of the records and setting NULL for the records (skill columns) that have no skills.

Something like this:

    SELECT * FROM pivot_table pt
RIGHT JOIN skill s ON s.id = pt.skillId
RIGHT JOIN resume r ON r.id = pt.resumeId

Result

id	resumeId	skillId	level	s_id	s_name	r_id
1	1	1	Advanced	1	.NET	1
2	1	2	Advanced	2	Azure	1
3	2	3	Advanced	3	JS	2
4	3	null	null	null	null	null

TypeORM

TypeORM does not support right join

Relevant StackOverflow

Valid TypeORM argument - Github issue

Solution

    SELECT * FROM (SELECT * FROM "resume") R1 
LEFT JOIN (SELECT * FROM "resume_to_skill") R2 ON (R1.ID = R2."resumeId") 
LEFT JOIN (SELECT * FROM "skill") S1 ON (S1.ID = R2."skillId")

Models

Resume.entity.ts


@Entity()
@Unique(['slug'])
export class Resume {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  slug: string;

  @OneToMany(() => ResumeToSkill, (resumeToSkill) => resumeToSkill.resume)
  resumeToSkill!: ResumeToSkill[];
}

Skill.entity.ts

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

  @Column()
  name: string;

  @OneToMany(() => ResumeToSkill, (resumeToSkill) => resumeToSkill.skill)
  resumeToSkill!: ResumeToSkill[];
}

ResumeToSkill.entity.ts

@Entity()
@Unique(['resume', 'skill'])
export class ResumeToSkill {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => Resume, (resume) => resume.resumeToSkill, {
    onDelete: 'CASCADE',
  })
  resume: number;

  @ManyToOne(() => Skill, (skill) => skill.resumeToSkill, {
    onDelete: 'CASCADE',
  })
  skill: string;

  @Column()
  level: string;
}

Request

I tried researching on how to create such a query using TypeORM, but no result. I struggle to figure out how to start with the "resume" table and then left join the subqueries. I would really appreciate it If you could point me to the right direction or help me resolve this.

I think you are overthinking the solution query. It can be simplified to: SELECT * FROM "resume" R1 LEFT JOIN "resume_to_skill" R2 ON R1.ID = R2."resumeId" LEFT JOIN "skill" S1 ON S1.ID = R2."skillId". — The Impaler
– The Impaler, Commented Oct 31, 2022 at 20:52
Yeah, you are certainly correct about that. Thanks! The biggest problem is not the PostgreSQL query, but the TypeORM equivalent. Since the Many-To-Many contains the "custom property - level" the models are created with OneToMany and ManyToOne. Which means - Resume (and Skill) table has resumeToSkil OneToMany rel towards ResumeToSkill pivot table, while ResumeToSkill pivot table has ManyToOne on both Resume and Skill. And I couldn't figure out the way to create the queryBuilder in such a way. — Lazar Stankovic
– Lazar Stankovic, Commented Oct 31, 2022 at 21:07

wahyutriu · Accepted Answer · 2022-11-01 13:04:04Z

1

Maybe like this:

//Entity

@Entity('resume')
@Unique(['slug'])
export class Resume {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  slug: string;

  @OneToMany(() => ResumeToSkill, (resumeToSkill) => resumeToSkill.resume)
  resumeToSkill!: ResumeToSkill[];
}

@Entity('skill')
export class Skill {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => ResumeToSkill, (resumeToSkill) => resumeToSkill.skill)
  resumeToSkill!: ResumeToSkill[];
}

@Entity('resume_to_skill')
@Unique(['resume', 'skill'])
export class ResumeToSkill {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => Resume, (resume) => resume.resumeToSkill, {
    onDelete: 'CASCADE',
  })
  resume: number;

  @ManyToOne(() => Skill, (skill) => skill.resumeToSkill, {
    onDelete: 'CASCADE',
  })
  skill: string;

  @Column()
  level: string;
}

// Service

.....
constructor(
    // add repository Resume 
    @InjectRepository(Resume)
    private _repo: Repository<Resume>,
  ) {}
.....
async findAll() {
    const entity = await this._repo
      .createQueryBuilder('resume')
      .select(
        'resume.id as rs_id, resume.name as rs_name, resume.slug, rts.*, rs.*',
      )
      .leftJoin('resume.resumeToSkill', 'rts')
      .leftJoin('rts.skill', 'rs')
      .getRawMany();

    return entity;
}

Result:

edited Nov 1, 2022 at 13:04

answered Nov 1, 2022 at 12:30

wahyutriu

444 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lazar Stankovic Over a year ago

Hi, thank you for your answer! This could work. i am getting this error now "TypeORMError: Relation with property path resumeToSkill in entity was not found." which is weird, it's as if there is no resumeToSkill OneToMany in the resume.entity.ts and skill.entity.ts I am not sure what could be the problem.

wahyutriu Over a year ago

Hi there, FYI i have revised my answer by adding the entity. Please check this out and retry. Hope it helps!

Lazar Stankovic Over a year ago

Hi there! Thanks, for your responses. I really appreciate it. Unfortunately, this does not fix the problem, even though i have the same entities and the same way of querying. I think i'll have to use the .query() from TypeORM. I'll try something else, but the task time is ticking and I have to move on. Thank you!

Lazar Stankovic Over a year ago

Hi there, I want to notify you that I managed to resolve this problem. Your comment helped a lot. It was a problem with the repository at the end. Everything is fine now. Thanks, again!

Collectives™ on Stack Overflow

Nested SELECT with LEFT JOIN - TypeORM, NestJS and PostgreSQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related