how to innerJoin in Typeorm NestJs ,I'm newbie with typeorm and nestjs

Question

MyProduct Entity

@Entity({ name: 'products' })
export class ProductEntity extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  product_name: string;

  @Column({ nullable: true, default: 0 })
  unit_qty: number;

  @Column({ nullable: true, default: 0 })
  unit_price: number;

  @Column()
  size: number;

  @Column({ nullable: true })
  cost: number;

  @Column({ type: 'boolean', default: false })
  status: boolean;

  @ManyToOne(
    () => ProductCategoryEntity,
    (productCategoryEntity) => productCategoryEntity.product,
  )
  @JoinColumn({ name: 'category_id', referencedColumnName: 'id' })
  category: ProductCategoryEntity;

  @Column()
  category_id: number;

  @BeforeInsert()
  async lowerCase() {
    this.product_name = this.product_name.toLowerCase();
  }
}

**My Product Category Entity**

@Entity({ name: 'product_category' })
export class ProductCategoryEntity extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  category: string;

  @OneToMany(() => ProductEntity, (productEntity) => productEntity.category)
  product: ProductEntity;

  @BeforeInsert()
  async lowerCase() {
    this.category = this.category.toLowerCase();
  }
}

My Product Service The code I want to join table using createQueryBuilder any example ?

findAll(option: IPaginationOptions): Observable<Pagination<ProductEntity>> {
    const queryBuilder = this.productRepo
      .createQueryBuilder('product')
      .innerJoinAndSelect();

    return from(paginate<ProductEntity>(queryBuilder, option)).pipe(
      map((products) => products),
      catchError(() => throwError(new InternalServerErrorException())),
    );
  }

how can I join the product table and product category together?

here is the raw query " select * from products p inner join product_category pc on pc.id=p.category_id; "

I'm Using Nestjs Typeorm+ Postgresql*

Youba Youba · Accepted Answer · 2021-01-19T09:05:37

You only need to specify the table you want to join:

findAll(option: IPaginationOptions): Observable<Pagination<ProductEntity>> {
const result = await this.productRepo
  .createQueryBuilder('product')
  .innerJoinAndSelect('product.category','category')
  .getMany();
 console.log(result);  // check the result
return from(paginate<ProductEntity>(queryBuilder, option)).pipe(
  map((products) => products),
  catchError(() => throwError(new InternalServerErrorException())),
);
 }

For more information visit Inner and left joins

how to innerJoin in Typeorm NestJs ,I'm newbie with typeorm and nestjs

1 Answers