Using experimental CTE

[ahartman]

What did you do?

Built a query using recursive CTE

What did you expect to happen?

My solution works, and it replaces a lot of crappy Swift code to transform the data.
The SQL produces data that does not need any transformation at all.
However, the solution is not very 'swifty', any ideas how to improve?

What happened instead?

Environment

GRDB flavor(s): (GRDB)
GRDB version: master
Installation method: (SPM)
Xcode version: latest
Swift version: 5
Platform(s) running GRDB: (MacCatalyst)
macOS version running Xcode: latest

Demo Project

This is about a graph, showing the frequency of waiting times for my wife's patients, i.e., the difference between the create data and the visit date of an visit in the Mac Calendar.

The issue in the graph data is that the data range may not be continuous, i.e., for a given time period, there are no visits with a waiting time of X weeks, like in the image for 0, 2 and 3 weeks of visit's age. Solved by left joining table 'alle' with a generated data range 'visitAgeRange'.

In addition to the graph shown in the image, I also want the sale graph as a running total up to 100%.
Solved with a window function in 'alleCum', using 'total' from another CTE table.

visitAges = try db.read { db in
                let visitAgeRangeCTE = CommonTableExpression(
                    recursive: true,
                    named: "visitAgeRange",
                    columns: ["type", "visitAge", "visitCount"],
                    literal:
                    """
                        WITH RECURSIVE
                            visitAgeRange(visitAge) AS (
                                VALUES(0)
                                UNION ALL
                                SELECT visitAge+1 FROM visitAgeRange WHERE visitage <
                                (
                                    SELECT max(visitAge)
                                    FROM visit
                                    WHERE visit.visitDate BETWEEN \(dateStart) AND \(dateEnd)
                                    AND visitCalendar IN ('Marieke', 'Marieke nieuwe')
                                )
                            ),
                            visitCountTotal AS (
                                SELECT CAST(count(*) AS FLOAT) AS total
                                FROM visit
                                WHERE visitDate BETWEEN \(dateStart) AND \(dateEnd)
                                AND visitCalendar IN ('Marieke', 'Marieke nieuwe')
                            ),
                            alle AS (
                                SELECT visitAgeRange.visitAge, count(visit.visitAge) AS visitCount
                                FROM visitAgeRange
                                LEFT JOIN visit
                                    ON visitAgeRange.visitage = visit.visitAge
                                    AND visit.visitDate BETWEEN \(dateStart) AND \(dateEnd)
                                    AND visitCalendar IN ('Marieke', 'Marieke nieuwe')
                                GROUP BY visitAgeRange.visitAge
                                )

                        SELECT 'alle' AS type, visitAge, visitCount
                        FROM alle
                        UNION
                        SELECT 'alleCum' AS type, visitAge,
                            SUM(visitCount / total) OVER (ORDER BY visitAge) AS visitCount
                        FROM alle, visitCountTotal

                        ORDER BY type, visitAge
                    """
                )
                let request = visitAgeRangeCTE.all().with(visitAgeRangeCTE)
                return try VisitAges1.fetchAll(db, request)

By the way, two notes:

• I had probleem with data interpolation and it took me a day to see the difference of a SQL Request with sql: and literal:; maybe you can emphasise that in the documentation.
• I found a small typo in the CTE documentation:

// An association from LeftRecord to rightCTE
let rightCTE = ...
let association = LeftRecord.association(
    to: rightCTE, 
    on: { left, right in
        left[Column("x")] = right[Column("y")]
    })

The = must be a double ==.

As said I replace a lot of complex Swift .map. .reduce and .filter to transform the original data.
However, any ideas to do the CTE in a more Swifty way?

Regards, André Hartman

[smhk]

" The = must be a double == "

nicely spotted !