• Factoring out a common subquery.
    • Grouping or filtering by a column derived in the CTE’s result set.
    • Writing recursive queries.

    To declare a query for use as a CTE, use cte() method, which wraps the query in a object. To indicate that a CTE should be included as part of a query, use the method, passing a list of CTE objects.

    For an example, let’s say we have some data points that consist of a key and a floating-point value. Let’s define our model and populate some test data:

    1. # First we'll declare the query that will be used as a CTE. This query
    2. # simply determines the average value for each key.
    3. cte = (Sample
    4.        .select(Sample.key, fn.AVG(Sample.value).alias('avg_value'))
    5.        .group_by(Sample.key)
    6.        .cte('key_avgs', columns=('key', 'avg_value')))
    8. # Now we'll query the sample table, using our CTE to find rows whose value
    9. # exceeds the average for the given key. We'll calculate how far above the
    10. # average the given sample's value is, as well.
    11. query = (Sample
    12.          .select(Sample.key, Sample.value)
    13.          .join(cte, on=(Sample.key == cte.c.key))
    14.          .where(Sample.value > cte.c.avg_value)
    15.          .order_by(Sample.value)
    16.          .with_cte(cte))

    We can iterate over the samples returned by the query to see which samples had above-average values for their given group:

    For a more complete example, let’s consider the following query which uses multiple CTEs to find per-product sales totals in only the top sales regions. Our model looks like this:

    1. class Order(Model):
    2.     region = TextField()
    3.     amount = FloatField()
    4.     product = TextField()
    5.     quantity = IntegerField()

    With Peewee, we would write:

    1. reg_sales = (Order
    3.              .group_by(Order.region)
    4.              .cte('regional_sales'))
    6. top_regions = (reg_sales
    7.                .select(reg_sales.c.region)
    8.                .where(reg_sales.c.total_sales > (
    9.                    reg_sales.select(fn.SUM(reg_sales.c.total_sales) / 10)))
    10.                .cte('top_regions'))
    12. query = (Order
    13.          .select(Order.region,
    14.                  Order.product,
    15.                  fn.SUM(Order.quantity).alias('product_units'),
    16.                  fn.SUM(Order.amount).alias('product_sales'))
    17.          .where(Order.region.in_(top_regions.select(top_regions.c.region)))
    18.          .group_by(Order.region, Order.product)
    19.          .with_cte(regional_sales, top_regions))

    Peewee supports recursive CTEs. Recursive CTEs can be useful when, for example, you have a tree data-structure represented by a parent-link foreign key. Suppose, for example, that we have a hierarchy of categories for an online bookstore. We wish to generate a table showing all categories and their absolute depths, along with the path from the root to the category.

    To list all categories along with their depth and parents, we can use a recursive CTE:

    1. # Define the base case of our recursive CTE. This will be categories that
    2. # have a null parent foreign-key.
    3. Base = Category.alias()
    4. level = Value(1).alias('level')
    5. path = Base.name.alias('path')
    6. base_case = (Base
    7.              .select(Base.id, Base.name, Base.parent, level, path)
    8.              .where(Base.parent.is_null())
    9.              .cte('base', recursive=True))
    11. RTerm = Category.alias()
    12. rlevel = (base_case.c.level + 1).alias('level')
    13. rpath = base_case.c.path.concat('->').concat(RTerm.name).alias('path')
    14. recursive = (RTerm
    15.              .select(RTerm.id, RTerm.name, RTerm.parent, rlevel, rpath)
    16.              .join(base_case, on=(RTerm.parent == base_case.c.id)))
    18. # The recursive CTE is created by taking the base case and UNION ALL with
    19. # the recursive term.
    20. cte = base_case.union_all(recursive)
    22. # We will now query from the CTE to get the categories, their levels,  and
    23. # their paths.
    24. query = (cte
    25.          .select_from(cte.c.name, cte.c.level, cte.c.path)
    26.          .order_by(cte.c.path))
    28. # We can now iterate over a list of all categories and print their names,
    29. # absolute levels, and path from root -> category.
    30. for category in query:
    31.     print(category.name, category.level, category.path)
    33. # Example output:
    34. # root, 1, root
    35. # p1, 2, root->p1
    36. # c1-1, 3, root->p1->c1-1
    37. # c1-2, 3, root->p1->c1-2