I have a database of companies in a hierarchical structure where all the companies are in the same table but each refer to a parent company that is referenced in a parent_id column. This value matches their respective parent company's company_id.

The pydantic model basically looks like this:

class Company(SQLModel, table=True):
    __tablename__ = "companies"

    company_id: str = Field(primary_key=True)
    parent_id: str = Field(
        foreign_key="Company.company_id",
        index=True
        )
    company_name: str = Field(index=True)
    company_type: str = Field(index=True)

The parent-child relationship can go down three levels deep, and each level has a different company_type, if it matters.

I want to SELECT a company by company_name and in the same response get all of its children and grandchildren (that are linked by their respective company_id and parent_id values).

I think using SQL one would use a "subquery" or a "self join", not sure which one is recommended and more performant. With regular SQL self-join I tested this:

SELECT C2.* FROM companies C1, companies C2 WHERE C1.company_name = 'xyz ltd.' AND C2.parent_id = C1.company_id;

and the response includes the children, but only the first level.

How can I perform this query (but retrieving all three levels of depth) using SQLModel or SQLAlchemy?