Query with 2 joins and subquery using kotlin-exposed

Question

I have such SQL query:

SELECT parent_id, e.season_number, max_seasons.max_episode_number, b.start_year FROM episodes e 
JOIN basics b USING(title_id)
JOIN (
    SELECT e.season_number, max(episode_number) AS max_episode_number FROM episodes e 
    WHERE e.parent_id = 944947
    GROUP BY e.season_number, parent_id
) AS max_seasons USING(season_number)
WHERE e.parent_id = 944947 AND e.episode_number = 1

Here is the response:

parent_id|season_number|max_episode_number|start_year|
--------+-------------+------------------+----------+
  944947|            1|                10|      2011|
  944947|            2|                10|      2012|
  944947|            3|                10|      2013|
  944947|            4|                10|      2014|
  944947|            5|                10|      2015|
  944947|            6|                10|      2016|
  944947|            7|                 7|      2017|
  944947|            8|                 6|      2019|

And I need to translate it to query using Exposed library and Kotlin. I extracted sub-query to separate variable subQuery with alias, and currently I try this one:

val episodeMax = episode.max()
val subQuery = EpisodeTable.slice(season, episodeMax)
    .select { parentId eq id }
    .groupBy(parentId, season).alias("sub_query")

EpisodeTable
    .join(BasicTable, JoinType.INNER,
        additionalConstraint = { BasicTable.titleId eq titleId })
    .join(subQuery, JoinType.INNER,
        additionalConstraint = { season eq subQuery[season] })
    .slice(parentId, season, subQuery[episodeMax], BasicTable.startYear)
    .select { (parentId eq id) and (episode eq 1) }
    .map { row ->
        Season(parentId = id,
            season = row[season],
            totalEpisodes = row[subQuery[episodeMax]],
            startYear = row[BasicTable.startYear])
    }

But got next error:

java.lang.IllegalStateException: Field not found in original table fields at org.jetbrains.exposed.sql.QueryAlias.get(Alias.kt:84) ~[exposed-core-0.38.2.jar:na]

So there is some problem with mapping resulting rows, but I can't figure out why.

Query with 2 joins and subquery using kotlin-exposed

0 Answers0