CODEWITHSUNDEEP
node.jspostgresqlnestjstypeorm
Lazar Stankovic
Lazar Stankovic

Reputation: 1

Nested SELECT with LEFT JOIN - TypeORM, NestJS and PostgreSQL

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

Tech used

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

  1. Return all resumes even if no skills are present
  2. 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.

Upvotes: 0

Views: 3383

Answers (1)

wahyutriu
wahyutriu

Reputation: 44

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: result

Upvotes: 1

Related Questions