Nested Multiset with Kotlin JOOQ and MySQL

Question

I'm having trouble constructing the correct query for two levels of nested collections -

The data shape as JSON...

[
    {
        "name": "My Super Region",
        "regions": [
            {
                "name": "My Region",
                "locations": [
                    {
                        "name": "My Location"
                    }
                ]
            }
        ]
    }
]

I looked at the intermittent Result from my JOOQ query and the shape of the data seemed alright to me. However, I'm having trouble successfully mapping that result into a Kotlin data class.

There's only so much that can be gleaned from the stack trace for something like this, so I'm having difficulty troubleshooting my cast errors. I'd appreciate any help sorting out what I'm missing!

fun getRegions(): List<SuperRegionRecord> {
    return DSL.using(dataSource, SQLDialect.MYSQL)
        .select(
            SUPER_REGIONS.ID,
            SUPER_REGIONS.NAME,
            multiset(
                select(
                    REGIONS.ID,
                    REGIONS.NAME,
                    multiset(
                        select(
                            LOCATIONS.ID,
                            LOCATIONS.NAME,
                        )
                        .from(LOCATIONS)
                        .where(LOCATIONS.REGION_ID.eq(REGIONS.ID))
                    ).`as`("locations")
                )
                .from(REGIONS)
                .where(REGIONS.SUPER_REGION_ID.eq(SUPER_REGIONS.ID))
            ).`as`("regions"),
        )
        .from(SUPER_REGIONS)
        .fetchInto(SuperRegionRecord::class.java)
}

data class SuperRegionRecord(
    val id: Int?,
    val name: String?,
    val regions: List<RegionRecord>?
)

data class RegionRecord(
    val id: Int?,
    val name: String?,
    val locations: List<LocationRecord>?
)

data class LocationRecord(
    val id: Int?,
    val name: String?
)

Error: java.lang.ClassCastException: class org.jooq.impl.RecordImpl3 cannot be cast to class com.abcxyz.repository.LocationsRepository$RegionRecord (org.jooq.impl.RecordImpl3 is in unnamed module of loader 'app'; com.abcxyz.LocationsRepository$RegionRecord is in unnamed module of loader io.ktor.server.engine.OverridingClassLoader$ChildURLClassLoader @f68f0dc)

Answer 1

Your generics (e.g. List<LocationRecord>) are being erased by the compiler, and jOOQ's runtime can't detect what your intention was when you were writing this query and assuming the DefaultRecordMapper will figure out how to map between a Result<Record3<...>> and a List (rawtype). While kotlin offers a bit more type information via its own reflection APIs, the DefaultRecordMapper isn't using that (and I'm not sure if it would be possible, still, though there's a feature request #15348).

Instead of using reflection to map your data, why not use ad-hoc conversion?

fun getRegions(): List<SuperRegionRecord> {
    return DSL.using(dataSource, SQLDialect.MYSQL)
        .select(
            SUPER_REGIONS.ID,
            SUPER_REGIONS.NAME,
            multiset(
                select(
                    REGIONS.ID,
                    REGIONS.NAME,
                    multiset(
                        select(
                            LOCATIONS.ID,
                            LOCATIONS.NAME,
                        )
                        .from(LOCATIONS)
                        .where(LOCATIONS.REGION_ID.eq(REGIONS.ID))
                    ).mapping(::LocationRecord) // 2)
                )
                .from(REGIONS)
                .where(REGIONS.SUPER_REGION_ID.eq(SUPER_REGIONS.ID))
            ).mapping(::RegionRecord), // 2)
        )
        .from(SUPER_REGIONS)
        .fetch(mapping(::SuperRegionRecord)) // 1)
}

Where:

1. is org.jooq.Records.mapping
2. is org.jooq.Field<org.jooq.Result<org.jooq.Record3<T1, T2, T3>>>.mapping or similar, from the jooq-kotlin extensions module