Stack Exchange
How to use query builder in Typeorm to generate this particular SQL query consisting of INNERJOIN, SELECT AS ? I cannot use rawQuery method in Typeorm
The associations are as follows: 
Category
@Entity()
export class Category extends BaseEntity {
    @Column({ type: "varchar", length: 255, nullable: true })
    name: string;

    @Column({ type: "varchar", nullable: true })
    description: string;

    @OneToMany(
        () => SubCategory,
        (subcategory) => subcategory.associatedCategory,
        {
        onDelete: "CASCADE",
        }
    )
    subCategories: SubCategory[];
}


SubCategory
@Entity()
export class SubCategory extends BaseEntity {
    @Column({ type: "varchar", length: 255, nullable: true })
    name: string;

    @Column({ type: "varchar", nullable: true })
    description: string;

    @ManyToOne(() => Category, (category) => category.subCategories, {
        eager: true,
        onDelete: "CASCADE",
    })
    associatedCategory: Category;

    @OneToMany(
        () => ProductGroup,
        (productGroup) => productGroup.associatedSubCategory,
        { cascade: true }
    )
    productGroups: ProductGroup[];
}


ProductGroup
@Entity()
export class ProductGroup extends BaseEntity {
    @Column({ type: "varchar", length: 255, nullable: true })
    name: string;

    @Column({ type: "varchar", nullable: true })
    description: string;

    @ManyToOne(() => SubCategory, (subCategory) => subCategory.productGroups, {
        onDelete: "CASCADE",
    })
    associatedSubCategory: any;

    @OneToMany(() => Product, (product) => product.associatedProductGroup)
    products: Product[];
}


Product
@Entity()
    export class Product extends BaseEntity {
    @Column({ type: "varchar", length: 255, nullable: true })
    name: string;

    @Column({ type: "varchar", length: 255, nullable: true })
    description: string;

    @ManyToOne(() => ProductGroup, (productGroup) => productGroup.products)
    associatedProductGroup;
}

I want to get products whose name,description or whose associated Category or SubCategory or ProductGroup's name,description include 'abc'. I wanted to generate the following SQL query using query builder in Typeorm:
SELECT combined.*
FROM
    (SELECT 
        p.*,
        p.name as pname,p.description as pdescription,
        pg.name as pgname,pg.description as pgdescription,
        sc.name as scname,sc.description as scdescription,
        c.name as cname,c.description as cdescription
    FROM
        product p
    
        INNER JOIN product_group pg 
        ON "p"."associatedProductGroupId"=pg.id

        INNER JOIN sub_category sc 
        ON pg."associatedSubCategoryId"=sc.id

        INNER JOIN category c 
        ON sc."associatedCategoryId"=c.id
    ) as combined

WHERE
    combined.pname LIKE '%abc%' or 
    combined.pdescription LIKE '%abc%' or 
    combined.pgname LIKE '%abc%' or
    combined.pgdescription LIKE '%abc%' or 
    combined.scname LIKE '%abc%' or 
    combined.scdescription LIKE '%abc%' or
    combined.cname LIKE '%abc%' or 
    combined.cdescription LIKE '%abc%';

The parts that I am strugging is:

Using the innerJoin properly here for nested joins
Generating proper for columns and table name
SELECT (...  p.name as pname ... )
FROM ( ... ) as tablealias

I cannot use raw query generator as I need to add further operations on the builder.
