Select soft deleted record when using left join nestjs

Question

I have an order table with relation address

@Entity('orders')
export class Order {
  ...
  @ManyToOne(() => Address, (address) => address.orders)
  address: Address;
  ...
}

@Entity('addresses')
export class Address {
  ...
  @DeleteDateColumn()
  deletedAt?: Date;
  ...
}

address table using soft-deleted, If I using query below I can't get soft-deleted address record

const query = this.createQueryBuilder('order')
      .leftJoinAndSelect('order.address', 'address');

I want to get all order with address either soft-deleted or not. Have any query for this issue?

Answer 1

Use the following syntax:

const query = this.createQueryBuilder('order')
  .withDeleted()
  .leftJoinAndSelect('order.address', 'address')
  .where('order.deletedAt is NULL')

Make sure to add .withDeleted before your join, else the deleted Address entities will not be returned. If order can be soft deleted as well, make sure to explicitly select the non-deleted entities as well to exclude it from the withDeleted condition.