Add field 'startYear' to type 'Season'

[unrec]

Update type Season:

type Season {
    season: Int
    totalEpisodes: Int
    startYear: Int
}

To get this data within' single SQL this query is to be run:

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

Corresponding kotlin-exposed query failed for mapping:

fun findSeasonsByTitleId(id: Int): List<Season> = transaction {
  val episodeMax = episode.max()
  val subQuery = EpisodeTable.slice(season, episodeMax)
	.select { parentId eq id }
	.groupBy(season, parentId).alias("sub_query")

  EpisodeTable
	.join(subQuery, INNER, season, subQuery[season])
	.join(BasicTable, INNER, BasicTable.titleId, titleId)
	.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])
		}
}

See question on stackoverflow here.

Same was tried to be resolved with this code, but it produces N+1 SQL separate queries:

fun findSeasonStartYear(season: Season): Short? = {
	val parentId = season.parentId
	val seasonNum = season.season
	val startId = episodeRepository.findStartEpisodeIdForSeason(parentId!!, seasonNum!!)
	return basicRepository.getStartYearForTitleId(startId!!)
}

fun getStartYearForTitleId(id: Int) = transaction {
	BasicTable.slice(startYear)
		.select { titleId eq id }
		.map { it[startYear] }
		.firstOrNull()
}