Delete / Update Cascade Feature

[mystyle2006]

Is your feature request related to a problem? Please describe.
In conclusion, the cascade function should be strengthened to use nestjs-query more efficiently.

In the case of Read / Create, Cascade has confirmed that there is no problem.

However, cascade does not seem to work normally when deleting and modifying.

Suppose you are creating a Todo app.

Two entities are required. TodoItem / SubTask.
An item can have multiple subtasks. That is the relationship of oneToMany.

Then, when the TodoItem is deleted, we expect that the Subtask will also be deleted due to Cascade.

But I get an error as below.

If you look at the query, an error occurs because of the foreign key constraint of the database because it deletes the TodoItem first, not the subtask first.

An error also occurs when correcting an incorrect query sequence similar to the one above.
(In case of modification, subtasks are always inserted)

Have you read the Contributing Guidelines?

(Write your answer here.)

Describe the solution you'd like
To solve this problem, the method used in typeorm-query.service.ts of the query-typeorm package has been overridden.
It may not be the best way, but after thinking about it, I couldn't come up with a better way other than this one.

when deleting method,

export class CustomTypeormQueryService<Entity> extends TypeOrmQueryService<Entity> {
  async deleteOne(id: string | number): Promise<Entity> {
    const relations = this.repo.metadata.relations.filter((i) => i.isCascadeSoftRemove).map((i) => i.propertyPath); // getting cascade relations
    const entity = await this.repo.findOne(id, {
      relations,
    });

    if (!entity) {
      throw new NotFoundException(`Unable to find ${this.EntityClass.name} with id: ${id}`);
    }

    if (this.useSoftDelete) {
      return this.repo.softRemove(entity as DeepPartial<Entity>);
    }
    return this.repo.remove(entity);
  }
}

when updating method (It's a bit long to consider all the exceptions below.)

• When all subtasks are deleted
• When only some of the subtasks are deleted
• When a newly created subtask exists

export class CustomTypeormQueryService<Entity> extends TypeOrmQueryService<Entity> {
  async updateOne(id: number | string, update: DeepPartial<Entity>, opts?: UpdateOneOptions<Entity>): Promise<Entity> {
      if (this.repo.hasId(update as unknown as Entity)) {
        throw new Error('Id cannot be specified when updating');
      }
  
      const cascadeRelations = this.repo.metadata.relations // getting cascade relation infos
        .filter((i) => i.isCascadeUpdate)
        .map((i) => {
          const primaryKeyObj = i.inverseEntityMetadata.columns.find((column) => column.isPrimary);
          const deletedAtObj = i.inverseEntityMetadata.columns.find((column) => column.isDeleteDate);
          return {
            relationName: i.propertyPath,
            targetEntity: i.inverseRelation.target,
            primaryColumn: {
              database: primaryKeyObj?.databaseName,
              property: primaryKeyObj?.propertyName,
            },
            deletedColumn: {
              database: deletedAtObj?.databaseName,
              property: deletedAtObj?.propertyName,
            },
            columnPropertyNames: i.inverseEntityMetadata.columns
              .filter((j) => !j.isCreateDate && !j.isUpdateDate)
              .map((j) => j.databaseName),
          };
        });
  
      const entity = await this.repo.findOne(id, {
        ...opts,
        relations: cascadeRelations.map((relation) => relation.relationName),
      });
  
      if (!entity) {
        throw new NotFoundException(`Unable to find ${this.EntityClass.name} with id: ${id}`);
      }
  
      // inserting or updating or removing subtasks
      if (cascadeRelations.length) {
        await Promise.all( // When it does not exist, it is created and modified when it is created.
          cascadeRelations.map((relation) =>
            this.repo
              .createQueryBuilder()
              .insert()
              .into(relation.targetEntity)
              .values(update[relation.relationName].map((i) => ({ ...i, [relation.deletedColumn.property]: null })))
              .orUpdate({
                conflict_target: [relation.primaryColumn.database],
                overwrite: relation.columnPropertyNames,
              })
              .updateEntity(false)
              .execute(),
          ),
        );
  
        await Promise.all(
          cascadeRelations.map((relation) => { // If there are cases that need to be deleted during the update
            const updateKeys = update[relation.relationName].map((i) => i[relation.primaryColumn.property]);
            const removeIds = entity[relation.relationName]
              .filter((i) => !updateKeys.includes(i[relation.primaryColumn.property]))
              .map((i) => i[relation.primaryColumn.property]);
            if (!removeIds.length) {
              return null;
            }
  
            return this.repo
              .createQueryBuilder()
              .softDelete()
              .from(relation.targetEntity)
              .where({
                [relation.primaryColumn.property]: In(removeIds),
              })
              .execute();
          }),
        );
  
        // removing subtasks in TodoItem
        cascadeRelations.forEach((relation) => {
          delete entity[relation.relationName];
          delete update[relation.relationName];
        });
      }
  
      // updating TodoItem
      return this.repo.save(this.repo.merge(entity, update));
    }
}

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.