Common table expressions

[grosch]

Depends on #206 being implemented first.

https://www.sqlservertutorial.net/sql-server-basics/sql-server-cte/

As an example, this is something I frequently do:

WITH cte AS (
    SELECT o.id order_id, SUM(w.amount * pt.amount) complete
    FROM work_completed w
    INNER JOIN order_points op ON w.order_point_id = op.id
    INNER JOIN points pt ON op.point_id = pt.id
    INNER JOIN orders o ON op.order_id = o.id
    GROUP BY o.id
)
SELECT ...
FROM xyz
JOIN cte on ...

[tanner0101]

Some initial thoughts:

final class BarCTE: ModelCTE {
    @Aggregate(.count, \.$id)
    var count: Int

    @Aggregate(.max, \.$age)
    var maxAge: Int?
}

Foo.query(on: db)
    .join(BarCTE.self, on: \Foo.$id == \Bar.$foo.$id) {
        $0.group(by: \.$country).group(by: \.$city)
    }
    .filter(BarCTE.self, \.$name == ...)
    .all()