TypeORM - left joining without "deletedAt IS NULL"

Question

Been searching this one for a while now, not sure if this is something unorthodox in the SQL world but I am trying to do a left join that counts with not null "deletedAt" column, but I cannot seem to find a way to do a left join which includes deletes.

This is just a dummy example that illustrates what I am trying to do, in this case I want to retrieve the person's information as well as the job's, but the job has been soft-deleted.

this.createQueryBuilder('person')
  .leftJoinAndSelect('person.job', 'job')

The SQL query produced by this implicitly adds "IS NOT NULL" on the left join but I have this scenario in which I need the information from a record that is indeed deleted.

I haven't been able to find a way to include this as you would do it at the root level by including.

.withDeleted();

Anyone has any tips?

Answer 1

In my tests, TypeOrm generates a different query depending on whether .withDeleted() is before or after .leftJoinAndSelect()

// Normal case: Exclude both soft-deleted 'Person' and 'Job':
        this.createQueryBuilder("Person")
        .leftJoinAndSelect('Person.job', 'Job');

// 'withDeleted() after the join: Include soft-deleted 'Person' BUT EXCLUDE soft-deleted 'Job':
        this.createQueryBuilder("Person")
        .leftJoinAndSelect('Person.job', 'Job')
        .withDeleted();

// 'withDeleted() before the join: Include both soft-deleted 'Person' and 'Job':
        this.createQueryBuilder("Person")
        .withDeleted()
        .leftJoinAndSelect('Person.job', 'Job');

So if you want to exclude the soft-deleted 'Person' but include the soft-deleted 'Job' (what I would expect), you can use withDeleted before the join (which will include everything) and add your own condition to then exclude soft-deleted 'Person' (i.e. add condition "Person.deletedAt IS NULL"). This way you don't need to do a complete RAW query:

// 'withDeleted() before the join with extra condition: Exclude soft-deleted 'Person' BUT INCLUDE soft-deleted 'Job':
        this.createQueryBuilder("Person")
        .withDeleted()
        .andWhere("Person.deletedAt Is Null")
        .leftJoinAndSelect('Person.job', 'Job');

(I tested with both TypeORM 0.2.30 and 0.2.31 and saw the same behaviour).