Support `FILTER/WHERE` and `ORDER BY` in aggregate expressions in Postgres dialect

matobet commented 1 year ago

SQLDelight Version

2.0.0

SQLDelight Dialect

Postgres

Describe the Bug

The following query does not compile:

selectArticles:
SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt
     , COALESCE (array_agg (DISTINCT tag ORDER BY tag) FILTER (WHERE tag IS NOT NULL), '{}') AS articleTags
FROM articles
LEFT JOIN tags ON articles.id = tags.article_id
JOIN users ON articles.author_id = users.id
GROUP BY articles.id, users.id;

:generateMainSqlDelightInterface fails with:

Compiling with dialect app.cash.sqldelight.dialects.postgresql.PostgreSqlDialect

/home/matobet/projects/kotlin/ktor-arrow-example/src/main/sqldelight/io/github/nomisrev/sqldelight/Articles.sq: (44, 41): '(', ')', '.', <binary like operator real>, <jsona binary operator real>, <jsonb binary operator real>, BETWEEN or IN expected, got 'ORDER'
43    SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt
44         , COALESCE (array_agg (DISTINCT tag ORDER BY tag) FILTER (WHERE tag IS NOT NULL), '{}') AS articleTags
                                               ^^^^^
45    FROM articles
46    LEFT JOIN tags ON articles.id = tags.article_id
47    JOIN users ON articles.author_id = users.id
48    GROUP BY articles.id, users.id

whereas the equivalent query works fine in Postgres.

Ideally the whole specification of aggregate expressions would be supported.

Stacktrace

No response

griffio commented 1 year ago

There are several issues to be considered 🌵

Aggregate expressions apply an aggregate function across rows and are maybe similar to Window Functions
Aggregate functions return a typed array column (numeric, text, date/time)
SqlDelight doesn't implement the Jdbc Array type PgArray
SqlDelight function resolvers would not work for aggregate functions as resolvers only have a fixed return type (e.g Text, Integer).

Gepro83 commented 8 months ago

this limitation makes me reconsider using sqldelight in the backend. We have a datamodel that relies on quite a bit of joins and the array_agg function would make many use cases 3x+ faster...

GeorgProhaska commented 8 months ago

maybe just allow string_agg ? doesnt that just return a text?

griffio commented 8 months ago

The string_agg is defined already but is limited to a function call such as: SELECT string_agg(city_name, ',') FROM City; to concatenate strings and place a separator between them.

The rest of the syntax should be possible to add, as is similar to the existing WINDOW function syntax - I will take a look to see if both string_agg and array_agg can be 🍖 fleshed out to be useful. Don't know until I try though.

sqldelight / sqldelight