https://kotlinlang.org logo
#squarelibraries
Title
# squarelibraries
m

Martin Beták

10/08/2023, 5:51 PM
Hi all, I'm trying to use SqlDelight 2.0.0 with Postgres dialect on JVM and trying to write the following query:
Copy code
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
Copy code
/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`:
Copy code
/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?
g

griffio

10/08/2023, 6:16 PM
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 🗝️
Copy code
, 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 😼
Copy code
(array_agg(tags.tag))
This does compile anyway with
COALESCE (array_agg..
Raise 🎫 for
FILTER
and
array_agg
https://github.com/cashapp/sqldelight/issues
m

Martin Beták

10/08/2023, 6:56 PM
@griffio thanks! that moved me a bit closer, although for the
COALESCE
column I get generated mapper parameter with type `String`:
Copy code
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?
g

griffio

10/09/2023, 8:22 AM
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}"
m

Martin Beták

10/09/2023, 12:22 PM
that would be for column definition but how does one do that for the
COALESCE
expression within a
SELECT
query?
Copy code
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
g

griffio

10/09/2023, 2:14 PM
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