SQLite group_concat(distinct...)

[Kevin Jones]

Hi Lukas,

I have a JOOQ query on SQLIte which looks like this:

    select(
                PLAYERSTEAMS.PLAYERID,
                TEAMSMATCHTYPES.MATCHTYPE,
                groupConcatDistinct(TEAMS.NAME).`as`("Teams")
            ).from(PLAYERSTEAMS)

....

The SQL this generates is:

select PlayersTeams.PlayerId,
                                TeamsMatchTypes.MatchType,
                                group_concat(distinct Teams.Name, ',') as Teams
                         from PlayersTeams

....

But this doesn't run against SQLite (at least on my version which is 3.45.3.0.

I *think* the SQL should be

select PlayersTeams.PlayerId,
                                TeamsMatchTypes.MatchType,
                                group_concat(distinct Teams.Name) as Teams
                         from PlayersTeams

as group_concat distinct in SQLIte can only use the default separator (I think).

This is the Kotlin DSL, Jooq version 3.19.7

--

Kevin Jones

KnowledgeSpike

[Lukas Eder]

Hi Kevin,

Thanks for your message. The method was introduced with jOOQ 3.18:

https://github.com/jOOQ/jOOQ/issues/13986

As you can see from the @Support annotation on the method, we don't support it yet for SQLite:

https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/impl/DSL.html#groupConcatDistinct(org.jooq.Field)

There's no reason not to support the function, of course. I've added a feature request:

https://github.com/jOOQ/jOOQ/issues/16666

You can use plain SQL templating as a workaround, or DSL.aggregateDistinct()

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAKT%3DYsPy71JgLPPMnY%3DhroEmy-cXT0Er%2BuaJ4QrmVRBak05PiQ%40mail.gmail.com.

[Kevin Jones]

Thanks Lucas,

Kevin Jones

KnowledgeSpike

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7CO13M%2B2ucsb_6o%3DBzJ430s1JOpkhiVYdE6bVZeSS8zA%40mail.gmail.com.