Martin Beták
10/08/2023, 5:51 PMselectArticles:
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
10/08/2023, 6:16 PMFILTER
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/issuesMartin Beták
10/08/2023, 6:56 PMCOALESCE
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?griffio
10/09/2023, 8:22 AMtag 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
10/09/2023, 12:22 PMCOALESCE
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
10/09/2023, 2:14 PMList<String>
, not the the tag column type.
I will have to look into it 👓 - the function resolvers are quite limited