Updating a parent of a record with children to a new parent closure table

[johnnysinger]

Issue description

When updating a parentRecords parent, the resulting parents of the parentRecord is incorrect

Expected Behavior

when updating a parent records parent to a new grandParentRecord i expect the result value of the parentRecords parents to equal [parentRecord, newGrandParentRecord]

Actual Behavior

when updating a parent records parent to a new grandParentRecord the result value of the parentRecords parents equal [parentRecord, oldGrandParentRecord, newgrandParentRecord]

Steps to reproduce

    let grandParent = await manager.create(name: "grandParent")
    grandParent = await manager.save(grandParent)
    let newGrandParent = await manager.create(name: "newGrandParent")
    newGrandParent = await manager.save(newGrandParent)
    let parent = await manager.create(name: "parent")
    parent = await manager.save(parent)
    let child = await manager.create(name: "child")
    child = await manager.save(child)

    parent.parent = grandParent
    await manager.save(parent)
    child.parent = parent
    await manager.save(child)

    let updatedParent = await manager.preload(this.entityClass, {...parent, parent: newGrandParent })
    updatedParent = await manager.save(updatedParent)

    const parentList = await manager.getTreeRepository<Entity>(this.entityClass).findAncestors(updatedParent)

My Environment

Dependency	Version
Operating System	macOS 13.4.1 (c)
ts-node version	10.9.1
Typescript version	5.2.2
TypeORM version	10.0.2
NestJs	10.3.2

Additional Context

I am currently using a work around for now. Since the error seems to only occur during an update i call a helper function to manually alter the closure table during an update mutation.

  public async updateTreeParent(childEntity: Entity, input: Update, manager: EntityManager): Promise<Entity> {
    const existingRecord = await manager.query(`SELECT 1 FROM ${this.closureTableName} WHERE id_descendant = '${childEntity.id}' AND id_ancestor = '${input.parentId}'`);
    if (!existingRecord.length) await manager.query(`UPDATE ${this.closureTableName} SET id_ancestor = '${input.parentId}' WHERE id_descendant = '${childEntity.id}' AND id_ancestor = '${childEntity.parentId}'`);
    else {
      await manager.query(`DELETE FROM ${this.closureTableName} WHERE id_descendant = '${childEntity.id}' AND id_ancestor = '${childEntity.parentId}'`);
    }
    return childEntity;
  }

my entity extends this base entity

@ObjectType()
export abstract class BaseTreeEntity<T> extends BaseEntity implements IBaseTreeEntity {
  @TreeParent({ onDelete: 'CASCADE', })
  parent?: T | null;

  @Field(() => ID, { nullable: true, })
  parentId?: string | null;

  @TreeChildren({ cascade: true })
  children?: T[] | null;
}

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, but I don't know how to start. I would need guidance.