typeorm: Tree closure table is not updated on parent update

Issue type:

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

Database system/driver:

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

TypeORM version:

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

Steps to reproduce or a small repository showing the problem:

Closure table is not updated when previously created entity updates its parent.

import * as path from 'path';
import { createConnection, ConnectionOptions, getTreeRepository } from 'typeorm';
import { PrimaryGeneratedColumn, Column, Tree, TreeChildren, TreeParent, Entity } from 'typeorm';

@Entity()
@Tree("closure-table")
export class TestEntity {
	@PrimaryGeneratedColumn() id: number;
	@TreeChildren()
	children: TestEntity[];
	@TreeParent() parent: TestEntity;
}

var connectionOptions: ConnectionOptions = {
    type: 'sqlite',
    database: path.join(__dirname, 'test.db'),
    entities: [ TestEntity ],
    synchronize: true,
    logging: true,
    dropSchema: true
};

createConnection(connectionOptions).then(async (c) => {
    var repo = getTreeRepository(TestEntity);
    
    var parent1 = new TestEntity;
    await repo.save(parent1);
    var parent2 = new TestEntity;
    await repo.save(parent2);
    var child = new TestEntity;

    child.parent = parent1;
    await repo.save(child);
    console.log(...await repo.query(`SELECT * FROM ${repo.metadata.closureJunctionTable.name}`));
    
    child.parent = parent2;
    await repo.update(child.id, child);
    console.log(...await repo.query(`SELECT * FROM ${repo.metadata.closureJunctionTable.name}`));
});

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 26
Comments: 19 (3 by maintainers)

Commits related to this issue

Remove note that is no longer relevant This edit removes the note which links to the closed issue https://github.com/typeorm/typeorm/issues/2032, that is supposed to be fixed in this PR https://gith... — committed to ghassanmas/typeorm by ghassanmas 3 years ago
docs: Remove note in TreeEntity documentation about #2032 (#7777) Removes the note which links to the closed issue https://github.com/typeorm/typeorm/issues/2032, that is supposed to be fixed in thi... — committed to typeorm/typeorm by ghassanmas 3 years ago

Most upvoted comments

https://github.com/typeorm/typeorm/blob/c654d5228ccdd867557cf0754e13d47cc2211186/src/repository/TreeRepository.ts#L13

I guess ability to change parent hasn’t been implemented yet.

+10

SharmaTushar on May 23, 2018

Generic solution with repository

@EntityRepository(Entity)
class YourEntityRepository extends TreeRepository {

    async updateParent(entity, newParent) {
        const descendantColumn = this.metadata.closureJunctionTable.descendantColumns[0].databasePath;
        const ancestorColumn = this.metadata.closureJunctionTable.ancestorColumns[0].databasePath;
        const closureTableName = this.metadata.closureJunctionTable.tablePath;
        const ancestorReferencedColumn = this.metadata.closureJunctionTable.ancestorColumns[0].referencedColumn.databasePath;
        const parentPropertyName = this.metadata.treeParentRelation.propertyName;

        let currentParentId;
        try {
            const result = await getConnection().createQueryBuilder()
                .select([descendantColumn, ancestorColumn])
                .from(closureTableName, closureTableName)
                .where(`${descendantColumn} = :descendantId`, { descendantId: entity[ancestorReferencedColumn] })
                .andWhere(`${ancestorColumn} <> :descendantId`)
                .execute();

            if (result && result[0])
                currentParentId = result[0][ancestorColumn];
        } catch (e) { }

        if (!currentParentId && newParent) {
            // insert into closure table
            await this.createQueryBuilder()
                .insert()
                .into(closureTableName, [ancestorColumn, descendantColumn])
                .values({
                    [ancestorColumn]: { [ancestorReferencedColumn]: newParent[ancestorReferencedColumn] },
                    [descendantColumn]: { [ancestorReferencedColumn]: entity[ancestorReferencedColumn] },
                })
                .execute();

            entity[parentPropertyName] = newParent;
        }

        if (currentParentId && newParent && currentParentId !== newParent[ancestorReferencedColumn]) {
            // update parent in closure table
            await this.createQueryBuilder()
                .update(closureTableName)
                .set({
                    [ancestorColumn]: { [ancestorReferencedColumn]: newParent[ancestorReferencedColumn] },
                })
                .where(`${descendantColumn} = :descendantId`, { descendantId: entity[ancestorReferencedColumn] })
                .andWhere(`${ancestorColumn} = :ancestorId`, { ancestorId: currentParentId })
                .execute();

            entity[parentPropertyName] = newParent;
        }

        if (!newParent && currentParentId) {
            // delete parent 
            await this.createQueryBuilder()
                .delete()
                .from(closureTableName)
                .where(`${descendantColumn} = :descendantId`, { descendantId: entity[ancestorReferencedColumn] })
                .andWhere(`${ancestorColumn} = :ancestorId`, { ancestorId: currentParentId })
                .execute();

            entity[parentPropertyName] = null;
        }

        await this.save(entity);
    }
}

eilrix on Mar 5, 2021

I think this should be explicitly pointed out in the document.

linfan68 on Oct 2, 2018

Seems this is not implemented on materialized-path either or i’m misunderstanding something. It updates the column ‘mpath’ but it only writes the start as if the object i’m inserting is the parent of the tree, even though the parentId column is getting updated with the correct value.

DDevil97 on Apr 21, 2021

Tested code, it works.

const sql = `UPDATE function_closure SET id_ancestor =${newParentId}  WHERE id_ancestor = ${oldParentId} AND id_descendant =${id} `;
        
        const result = await getManager().query(sql);

Xin2050 on Jan 17, 2021