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 modelalias to represent the second instance of the table in a query. Consider thefollowing model:

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

What if we wanted to query all categories whose parent category isElectronics. 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 tospecify the join condition using the on keyword argument. In this case weare joining the category with its parent category.

Using subqueries

Another less common approach involves the use of subqueries. Here is anotherway we might construct a query to get all the categories whose parent categoryis 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 lookupwhich will generate the appropriate SQL expression:

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