Peewee get all descendants

I am using peewee as a ORM in python (in combination with postgresql) and I am trying to query every descendant of a given id out of a table with self referencing (parent-child)

What is the best way to achieve this?

Edit:

I already setup the peewee model class:

class Customer(BaseModel):
    customerid = PrimaryKeyField()
    name = CharField(null=False)
    parentid = ForeignKeyField(db_column='parentid',
                           null=True,
                           rel_model='self', to_field='customerid')
    customer_type_customertypeid = ForeignKeyField(
        db_column='customer_type_customertypeid',
        null=False,
        rel_model=CustomerType, to_field='customertypeid')

class Meta:
    db_table = 'customer'

Now I want to query every child of a given customer by id and convert that to a dict

C1 = dbhandler.Customer.alias()

try:
    return [model_to_dict(model) for model in
        C1.select().where(...)]
except:
     return {}

But I can't think of a query that would achieve this without using a CTE. But since peewee doesn't have support for CTE's I am out of ideas. I did see that peewee has a .raw(..) function, in which you can put real sql queries, but that would just destroy the advantage of using an ORM.

Solution

You'll probably want to execute a recursive common table expression. Peewee 2.x does not support this yet, but it is in the 3.0 which is currently unreleased as it's got a lot of changes and I haven't finalized some of the APIs. Sorry! Trying to get that out.

Anyways, you can use MyModel.raw('WITH ... ') to run a recursive CTE and receive model instances.