Hi all, I'm trying to use SqlDelight 2.0.0 with Po...

# squarelibraries

[Martin Beták]

Hi all, I'm trying to use SqlDelight 2.0.0 with Postgres dialect on JVM and trying to write the following query:

selectArticles:
SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt
     , COALESCE (array_agg (tags.tag) FILTER (WHERE tags.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;

this fails on :generateMainSqlDelightInterface with

/home/matobet/projects/kotlin/ktor-arrow-example/src/main/sqldelight/io/github/nomisrev/sqldelight/Articles.sq: (44, 38): ')', <binary like operator real>, BETWEEN, IN or WITHIN expected, got 'FILTER'
43    SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt
44         , COALESCE (array_agg (tags.tag) FILTER (WHERE tags.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

When I try to simplify the expression I still get `unknown function array_agg`:

/home/matobet/projects/kotlin/ktor-arrow-example/src/main/sqldelight/io/github/nomisrev/sqldelight/Articles.sq: (43, 114): Unknown function array_agg
43    SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt, array_agg(tags.tag)
                                                                                                                        ^^^^^^^^^^^^^^^^^^^
44    --      , COALESCE (array_agg (tags.tag) FILTER (WHERE tags.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

Are these things planned to be added to the Postgres dialect or is there some workaround I could use locally in the meantime?

[griffio]

Sadly, FILTER is not in the current PostgreSql grammar in sqldelight. As a work-around you could try -
Use CASE WHEN instead, the old way 🗝️

, COALESCE (array_agg (CASE WHEN tags.tag IS NOT NULL THEN tags.tag END) , '{}') AS articleTags

If you have a function that is not yet implemented you can surround in parentheses 😼

(array_agg(tags.tag))

This does compile anyway with COALESCE (array_agg..
Raise 🎫 for FILTER and array_agg https://github.com/cashapp/sqldelight/issues

[Martin Beták]

@griffio thanks! that moved me a bit closer, although for the COALESCE column I get generated mapper parameter with type `String`:

public fun <T : Any> selectArticles(mapper: (
    id: ArticleId,
    slug: String,
    title: String,
    description: String,
    body: String,
    username: String,
    bio: String,
    image: String,
    createdAt: OffsetDateTime,
    updatedAt: OffsetDateTime,
    articleTags: String,  <----------
  )

It even "works" at runtime (in a sense it doesn't crash 😅), giving me the textual representation of the array "{tag1,tag2}" but is there perhaps a way how to explicitly state the type of that column, so that generated mapper uses List<String> or similar?

[Martin Beták]

also created https://github.com/cashapp/sqldelight/issues/4703

[griffio]

You can use tag TEXT AS List<String> NOT NULL https://cashapp.github.io/sqldelight/2.0.0/jvm_postgresql/types/#custom-column-types
Your custom mapper can decode the "{tag1,tag2}"

[Martin Beták]

that would be for column definition but how does one do that for the COALESCE expression within a SELECT query?

SELECT articles.id, slug, title, description, body, users.username, users.bio, users.image, createdAt, updatedAt
     , COALESCE (array_agg (CASE WHEN tags.tag IS NOT NULL THEN tags.tag END) , '{}') AS articleTags

[griffio]

Ah Yes - you are right, only the aggregate function should return a List<String>, not the the tag column type.
I will have to look into it 👓 - the function resolvers are quite limited