Self-referential foreign keys

When creating a hierarchical structure it is necessary to create aself-referential foreign key which links a child object to its parent. Becausethe model class is not defined at the time you instantiate the self-referentialforeign key, use the special string 'self' to indicate a self-referentialforeign key:

class Category(Model):
    name = CharField()
    parent = ForeignKeyField('self', null=True, backref='children')

As you can see, the foreign key points upward to the parent object and theback-reference is named children.

Attention

Self-referential foreign-keys should always be null=True.

When querying against a model that contains a self-referential foreign key youmay sometimes need to perform a self-join. In those cases you can useModel.alias() to create a table reference. Here is how you might querythe category and parent model using a self-join:

Parent = Category.alias()
GrandParent = Category.alias()
query = (Category
         .select(Category, Parent)
         .join(Parent, on=(Category.parent == Parent.id))
         .join(GrandParent, on=(Parent.parent == GrandParent.id))
         .where(GrandParent.name == 'some category')
         .order_by(Category.name))