Self-joins

Peewee supports constructing queries containing a self-join.

Using model aliases

To join on the same model (table) twice, it is necessary to create a model alias to represent the second instance of the table in a query. Consider the following model:

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

What if we wanted to query all categories whose parent category is Electronics. One way would be to perform a self-join:

Parent = Category.alias()
query = (Category
         .select()
         .join(Parent, on=(Category.parent == Parent.id))
         .where(Parent.name == 'Electronics'))

When performing a join that uses a ModelAlias, it is necessary to specify the join condition using the on keyword argument. In this case we are joining the category with its parent category.

Using subqueries

Another less common approach involves the use of subqueries. Here is another way we might construct a query to get all the categories whose parent category is Electronics using a subquery:

Parent = Category.alias()
join_query = Parent.select().where(Parent.name == 'Electronics')
# Subqueries used as JOINs need to have an alias.
join_query = join_query.alias('jq')
query = (Category
         .select()
         .join(join_query, on=(Category.parent == join_query.c.id)))

This will generate the following SQL query:

SELECT t1."id", t1."name", t1."parent_id"
FROM "category" AS t1
INNER JOIN (
  SELECT t2."id"
  FROM "category" AS t2
  WHERE (t2."name" = ?)) AS jq ON (t1."parent_id" = "jq"."id")

To access the id value from the subquery, we use the .c magic lookup which will generate the appropriate SQL expression:

Category.parent == join_query.c.id
# Becomes: (t1."parent_id" = "jq"."id")