Skip to content
Advertisement

Create query builder that the source table (FROM) is a join table in TypeORM

I’m trying to implement the following SQL in TypeORM using QueryBuilder:

SELECT
  user_places.user_id,
  place.mpath
FROM
  public.user_root_places_place user_places
INNER JOIN
  public.place place
  ON place.id = user_places.place_id

The entities are:

@Entity()
export class User {
  @Column({ unique: true, primary: true })
  id: string;

  @ManyToMany(() => Place)
  @JoinTable()
  rootPlaces: Place[];
}

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

  @Column()
  mpath: string;
}

When you create a query builder you have to use some entity or table but the join table is “hidden” by TypeORM


I know I can replace the inner join table order and it will solve the problem but I’m looking for when the source table is the join table

Advertisement

Answer

If you don’t want to use the generated name just specify explicitly the join table name

@Entity()
export class User {
  @Column({ unique: true, primary: true })
  id: string;

  @ManyToMany(() => Place)
  @JoinTable({
    name: 'user_places' // <---
  })
  rootPlaces: Place[];
}

And then:

createQueryBuilder('user_places')
  .select(['user_places.userId', 'place.mpath'])
  .innerJoin(Place, 'place', 'place.id = user_places.place_id')
  .getMany();
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement