Query showing Field not found in original table fi...

# exposed

[Poulastaa]

Query showing Field not found in original table fields.
Explanation: Alias name not applying

code is the the thread

[Poulastaa]

exposed version: "0.58.0"


Am I doing something wrong.

This is the SQL query i want to achieve:

SELECT RankedSongs.songId, RankedSongs.countryId, RankedSongs.popularity FROM (
    SELECT 
        UniqueSongs.songId, 
        UniqueSongs.popularity, 
        UniqueSongs.countryId,
        ROW_NUMBER() OVER (PARTITION BY UniqueSongs.countryId ORDER BY UniqueSongs.popularity DESC)  `rank`
    FROM (
        SELECT DISTINCT Song.id  songId, SongInfo.popularity, Country.id  countryId
        FROM Song
        JOIN SongInfo ON SongInfo.songId = Song.id
        JOIN SongArtist ON SongArtist.songId = Song.id
        JOIN ArtistCountry ON ArtistCountry.artistId = SongArtist.artistId
        JOIN Country ON Country.id = ArtistCountry.countryId
    )  UniqueSongs
)  RankedSongs
WHERE RankedSongs.`rank` <= 6 
ORDER BY RankedSongs.countryId DESC, RankedSongs.popularity DESC;

This is the euivalant exposed query I tried out.

val songId = EntitySong.id.alias("songId")
        val popularity = EntitySongInfo.popularity.alias("popularity")
        val countryId = EntityCountry.id.alias("countryId")

        val uniqueSongs = kyokuDbQuery {
            EntitySong
                .join(
                    otherTable = EntitySongInfo,
                    joinType = JoinType.INNER,
                    additionalConstraint = {
                        EntitySong.id eq EntitySongInfo.songId
                    }
                )
                .join(
                    otherTable = RelationEntitySongArtist,
                    joinType = JoinType.INNER,
                    additionalConstraint = {
                        RelationEntitySongArtist.songId eq EntitySong.id as Column<*>
                    }
                )
                .join(
                    otherTable = RelationEntityArtistCountry,
                    joinType = JoinType.INNER,
                    additionalConstraint = {
                        RelationEntityArtistCountry.artistId eq RelationEntitySongArtist.artistId
                    }
                )
                .join(
                    otherTable = EntityCountry,
                    joinType = JoinType.INNER,
                    additionalConstraint = {
                        RelationEntityArtistCountry.countryId eq EntityCountry.id as Column<*>
                    }
                )
                .select(songId, popularity, countryId)
                .withDistinct(true)
                .alias("UniqueSongs")
        }
        val rank = kyokuDbQuery {
            RowNumber()
                .over()
                .partitionBy(uniqueSongs[countryId])
                .orderBy(uniqueSongs[popularity], SortOrder.DESC)
                .alias("`rank`")
        }
        val rankedSongs = kyokuDbQuery {
            uniqueSongs.select(
                uniqueSongs[songId],
                uniqueSongs[popularity],
                uniqueSongs[countryId],
                rank
            ).alias("RankedSong")
        }

        kyokuDbQuery {
            val query = rankedSongs
                .select(
                    rankedSongs[songId],
                    rankedSongs[popularity],
                    rankedSongs[countryId],
                )
                .where { rankedSongs[rank] lessEq longLiteral(COUNTRY_MOST_POPULAR_SONGS_LIMIT) }
                .orderBy(
                    rankedSongs[countryId] to SortOrder.DESC,
                    rankedSongs[popularity] to SortOrder.DESC,
                )

            println(query.prepareSQL(QueryBuilder(true)))

            exitProcess(1)
        }

this is spitting out error Field not found in original table fields.
on this part

rankedSongs
                .select(
                    rankedSongs[songId],
                    rankedSongs[popularity],
                    rankedSongs[countryId],
                )

if I try selecting all

val query = rankedSongs.selectAll()
    .where { rankedSongs[rank] lessEq longLiteral(COUNTRY_MOST_POPULAR_SONGS_LIMIT) }

println(query.prepareSQL(QueryBuilder(true)))

that works but spites out wrong query
This is the debugged query:

SELECT UniqueSongs.songId, UniqueSongs.popularity, UniqueSongs.countryId, `rank` FROM ( // this should not happen right ? :)
	SELECT 
		UniqueSongs.songId,
        UniqueSongs.popularity, 
        UniqueSongs.countryId, 
        ROW_NUMBER() OVER(PARTITION BY UniqueSongs.countryId ORDER BY UniqueSongs.popularity DESC) `rank` 
	FROM (
		SELECT DISTINCT Song.id songId, SongInfo.popularity popularity, Country.id countryId 
        FROM Song 
        INNER JOIN SongInfo ON  (Song.id = SongInfo.songId) 
        INNER JOIN SongArtist ON  (SongArtist.songId = Song.id) 
        INNER JOIN ArtistCountry ON  (ArtistCountry.artistId = SongArtist.artistId) 
        INNER JOIN Country ON  (ArtistCountry.countryId = Country.id)
	) UniqueSongs
) RankedSong 
WHERE RankedSong.`rank` <= 6;

What am i doing wrong ? NEED SOME HELP.
And also I tried this to

rankedSongs
                .select(
                    rankedSongs[uniqueSongs[songId]],
                    rankedSongs[uniqueSongs[popularity]],
                    rankedSongs[uniqueSongs[countryId]],
                )

No luck shows the same error. Field not found in original table fields.

[Poulastaa]

I was able to get the desired output like this:

val rankedSongs = kyokuDbQuery {
            uniqueSongs.select(
                uniqueSongs.fields[0],
                uniqueSongs.fields[1],
                uniqueSongs.fields[2],
                rank
            ).alias("RankedSong")
        }

        return kyokuDbQuery {
            rankedSongs
                .select(
                    rankedSongs.fields[0],
                    rankedSongs.fields[1],
                    rankedSongs.fields[2]
                )
                .where { rankedSongs[rank] lessEq longLiteral(COUNTRY_MOST_POPULAR_SONGS_LIMIT) }
                .orderBy(
                    rankedSongs.fields[2] to SortOrder.DESC,
                    rankedSongs.fields[1] to SortOrder.DESC,
                ).map {
                    DtoCountryPopularSong(
                        songId = it[rankedSongs.fields[0]].toString().toLong(),
                        countryId = it[rankedSongs.fields[2]].toString().toInt(),
                    )
                }
        }

still hoping for a cleaner solution.