Self-referential foreign keys
When creating a heirarchical structure it is necessary to create a self-referential foreign key which links a child object to its parent. Because the model class is not defined at the time you instantiate the self-referential foreign key, use the special string 'self'
to indicate a self-referential foreign 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 the back-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 you may sometimes need to perform a self-join. In those cases you can use Model.alias()
to create a table reference. Here is how you might query the 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))