find with relations returns soft-deleted entities

[Ollie1700]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

Apologies for opening an issue for this, it seems like I am probably missing something very obvious but essentially the issue is that soft-deleted entities are being returned as part of relations.

Example:

Say we have "User" and "Post" - user has many posts

1. const userWithPosts = await userRepo.findOne(1, { relations: [ 'posts' ] })
2. userWithPosts.posts will contain all posts, even ones that have been deleted

Am I missing something obvious here? This functionality works as expected when not using relations (e.g. in the above example, postRepo.find() would only return non-deleted items) but when using relations I am running into this issue.

[Ollie1700]

This behaviour is still a blocker for me at the moment. I'm wondering if anyone knows if it is intended, and if so what is the workaround? Or if it is known to be a bug I can look to make a PR for this one if someone can point me in the right direction.

Thanks again, I know your team must be very busy.

[andreimc]

@Ollie1700 we are seeing the same thing

[yorickdevries]

Same as #5838

[chenfanggm]

Same here.

[nshCore]

@Ollie1700 @chenfanggm is the field you use the @DeleteDateColumn() named 'deletedAt'?

Anything else wont work.

[chenfanggm]

@nshCore

@Ollie1700 @chenfanggm is the field you use the @DeleteDateColumn() named 'deletedAt'?

Anything else wont work.

Yea, I have named the @DeleteDateColumn() as deletedAt.
`@ObjectType()
export abstract class BaseEntity extends TypeormBaseEntity {
@field()
@CreateDateColumn({ type: 'timestamp with time zone' })
createdAt: Date;

@field()
@UpdateDateColumn({ type: 'timestamp with time zone' })
updatedAt: Date;

@field()
@DeleteDateColumn({ type: 'timestamp with time zone' })
deletedAt: Date;
}`

const post = await Post.findOne(id, { relations: [ 'creator', 'category', 'tags', 'threads', 'threads.creator', 'threads.replies', 'threads.replies.creator', 'threads.replies.replyToUser', ], order: { updatedAt: 'DESC', }, });

Even my thead is softDeleted, but it still come back as result for the query.

Thanks.

[marcinja]

I'm seeing a similar issue and also am using a column named `deletedAt

[riskidan]

Seeing the same problem

[KevinArce98]

Same problem, some solution?

[nebkat]

This is simply because the functionality is not implemented. The code that normally adds the filter for deleted items is here, and it does so for the main entity in the query only, not for the relations:

typeorm/src/query-builder/QueryBuilder.ts

Lines 663 to 672 in c4a36da

	const metadata = this.expressionMap.mainAlias!.metadata;
	// Adds the global condition of "non-deleted" for the entity with delete date columns in select query.
	if (this.expressionMap.queryType === "select" && !this.expressionMap.withDeleted && metadata.deleteDateColumn) {
	const column = this.expressionMap.aliasNamePrefixingEnabled
	? this.expressionMap.mainAlias!.name + "." + metadata.deleteDateColumn.propertyName
	: metadata.deleteDateColumn.propertyName;
	const condition = `${this.replacePropertyNames(column)} IS NULL`;
	conditionsArray.push(condition);
	}

It makes sense to filter out deleted One-to-Many/Many-to-Many relations, but it might not be the expected default behavior for a One-to-One/Many-to-One relation. If a user's "profile" has been deleted, you might still expect the relation to be loaded, especially if user.profile can't be null.

If someone wants to implement an option for this you can send in a PR and link to this issue, it shouldn't be too difficult. Start with SelectQueryBuilder and QueryBuilder.

---

BTW, please avoid commenting just to say you have the problem. The original issue has described what is wrong, unless you have something useful to add just 👍 it.

[klaaz0r]

What is the status of this? Seems like a fundamental feature to have would like to work on it but is anyone already fixing this?

[nebkat]

I am not aware of anyone working on this, you can submit a PR if you want.

[jdnielss]

Seeing the same problem

[lorenzosignoretti]

I know the issue has been closed, but I think the behaviour has remained the same even in 0.2.31

[klaaz0r]

@lorenzosignoretti exactly the same ting happening for me, it can be related with the skip/limit I also include maybe?

[mossa-Sammer]

it's not working neither using find options nor query builder, it's frustrating that I need to write a query with 6 joins manually :(

[mossa-Sammer]

Worked for me when using the query builder and placing the withDeleted function at the top like this

orderRepo
      .createQueryBuilder('order')
      .withDeleted()
      .innerJoin('order.kitchen', 'kitchen')
      .innerJoin('order.user', 'user')
      .innerJoin('order.orderStatus', 'orderStatus')
      .leftJoin('order.orderStatusLogs', 'orderStatusLogs')
      .innerJoinAndSelect('order.orderItems', 'orderItems')
      .leftJoinAndSelect('orderItems.foodListing', 'foodListing')
      .where('order.order_id = :orderId', { orderId })
      .getOne();

but still not working with find options

[xerxes235]

in most cases, you have to use innerJoinAndSelect instead of leftJoinAndSelect.

[tuananhlai]

Is there any workaround for this issue? It seems weird that such a basic feature is missing.

[EdsonRCJ]

Is there any update for this issue ? I'm facing the same.

[ryanhex53]

But I want the ManyToOne relation being loaded even it was soft-deleted, it returns null in version 0.2.45. How can I do ?

[ryanhex53]

But I want the ManyToOne relation being loaded even it was soft-deleted, it returns null in version 0.2.45. How can I do ?

For someone who need load soft-deleted relations, this is my approach.

async listAll(
  dto: PageableDto,
  where?: FindConditions<T>[] | string | ObjectLiteral,
  relations?: string[],
  select?: any[]
): Promise<PaginatedRes<T>> {
  where = where || {};
  const total = await this.repo.count({ where });
  const order: any = dto.by && dto.sort ? { [dto.by]: dto.sort } : {};
  // fix *where* to exclude soft-deleted
  if (Array.isArray(where)) {
    where.forEach((cond: ObjectLiteral) => {
      cond.deletedDate = IsNull();
    });
  } else if (typeof where != 'string') {
    where.deletedDate = IsNull();
  }
  const options: FindManyOptions<T> = {
    withDeleted: true, // force load relations include soft-deleted
    where,
    relations,
    select,
    order
  };
  if (dto.limit != -1) {
    options.take = dto.limit;
    options.skip = (dto.page - 1) * dto.limit;
  }
  const results = await this.repo.find(options);
  const pages = dto.limit == -1 ? 1 : Math.ceil(total / dto.limit);
  return {
    results,
    page: dto.page,
    pages,
    limit: dto.limit,
    total
  };
}

[kevin4dhd]

any solution? :/

[osellas]

Same here 😬

[amiralammohawk]

same