1

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?

noob
  • 328
  • 2
  • 13
  • The SQL `select` statement offers no way to do recursion to an indefinite depth. – BoarGules Apr 01 '22 at 10:48
  • How about a defined depth? I'm thinking 3 levels would be sufficient for my case. – noob Apr 01 '22 at 11:01
  • Related: https://stackoverflow.com/q/66895692/2144390 https://stackoverflow.com/a/66906504/2144390 – Gord Thompson Apr 01 '22 at 12:26
  • What about defining an originator field with the id of the first parent? Then if you have the originator id you can select all member records of the family at all levels? – Liquidgenius May 12 '22 at 12:04

0 Answers0