How to search tree structure with multiple types with TypeORM

Question

thanks for reading and provide any suggestions or any keyword I can do more research.

Quite new to TypeORM and have an issue on searching the tree structure with TypeORM.

The tree structure is a typical tree structure but it may have different node types (entity types/tables) by using different patterns (closure table and adjacency list) which makes it is more complex.

Let's say it is a company hierarchy tree, contains companies, departments and teams, each node type could be a different entity (database table), and link to its parent and children with the same type by using closure table, but with different child node by using adjacency list. For example the Company entity links to parentCompany and childCompany by using closure table, but to its departments by using adjacency list:

import {Entity, Tree, Column, PrimaryGeneratedColumn, TreeChildren, TreeParent, TreeLevelColumn} from "typeorm";

@Entity()
@Tree("closure-table")
export class Company {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @TreeChildren()
    children: Company[];

    @TreeParent()
    parent: Company;

    @OneToMany(() => Department, (entity) => entity.company)
    @IsInstance(Department, { each: true })
    public departments: Department[];
}

Similar for Department:

import {Entity, Tree, Column, PrimaryGeneratedColumn, TreeChildren, TreeParent, TreeLevelColumn} from "typeorm";

@Entity()
@Tree("closure-table")
export class Department {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @ManyToOne(() => Company, (entity) => entity.departments)
    @JoinColumn({ name: "company_id" })
    @IsInstance(Company)
    @IsOptional()
    public company?: Company;

    @TreeChildren()
    children: Department[];

    @TreeParent()
    parent: Department;

    @OneToMany(() => Team, (entity) => entity.team)
    @IsInstance(Team, { each: true })
    public teams: Team[];
}

and Teams:

import {Entity, Tree, Column, PrimaryGeneratedColumn, TreeChildren, TreeParent, TreeLevelColumn} from "typeorm";

@Entity()
@Tree("closure-table")
export class Team {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @ManyToOne(() => Department, (entity) => entity.teams)
    @JoinColumn({ name: "department_id" })
    @IsInstance(Department)
    @IsOptional()
    public department?: Department;

    @TreeChildren()
    children: Team[];

    @TreeParent()
    parent: Team;
}

I am going to do a search by name from all of the tree nodes. Did some research and investigation, I know TypeORM tree repository provides createDescendantsQueryBuilder is able to so:

const children = await repository
    .createDescendantsQueryBuilder("company", "companyClosure", parentCompany)
    .andWhere("company.name like '%stackoverflow%'")
    .getMany();

But it looks like only the company can be retrieved from the closure table, not the children departments and teams. My questions are:

1. Is tree repository able to retrieve all the node types with the createDescendantsQueryBuilder example?
2. If it is not supported, I think will have to cater the business logic myself, in that case will have to provide pagination myself as well?
3. If the search criteria apply to different column names in each table, how can I achieve that?

Answer 1

Did some research and investigation, from the closure table the descendants of a node can be easily retrieved.

I think I can do separate query for each type and union them all. The key point would be limit the search range then.

I can get the seed departments, and with the help of the closure table, all the relevant department can be retrieved and do the search.