OrderBy SubQuery field in Kotlin Exposed

Question

In Kotlin Exposed, when I try to add an OrderBy of a field from a join subquery I get the error: Field not found in original table fields.

Below Kotlin code runs successfully and generates the SQL below.

        val aggScoreColumn = object : Function<Int?>(IntegerColumnType()) {
                override fun toQueryBuilder(queryBuilder: QueryBuilder) {
                    queryBuilder {
                        append("${ScoredKeywordTable.score.avg()} + (${ScoredKeywordTable.score.count()} * .3)")
                    }
                }
            }

        val aggKeywordQuery = ScoredKeywordTable
            .slice(
                ScoredKeywordTable.scoredPageId,
                ScoredKeywordTable.score.avg(),
                ScoredKeywordTable.score.count(),
                ScoredKeywordRepository.AggScoreColumn
            )
            .select {
                ScoredKeywordTable.userId eq userId and
                        (ScoredKeywordTable.keywordId inList keywordIds)
            }
            .groupBy(ScoredKeywordTable.scoredPageId)
            .orderBy(aggScoreColumn, SortOrder.DESC)
            .alias("aggKeywordQuery")

        val result = Join(ScoredPageTable)
            .join(aggKeywordQuery, JoinType.INNER,
                additionalConstraint = { ScoredPageTable.id eq aggKeywordQuery[ScoredKeywordTable.scoredPageId] })
            .slice(ScoredPageTable.id, aggKeywordQuery[ScoredKeywordTable.scoredPageId])
            .selectAll()
            .orderBy(aggKeywordQuery[ScoredKeywordTable.scoredPageId], SortOrder.DESC)
            .toList()

SELECT scored_page.id, aggKeywordQuery.scored_page_id 
FROM scored_page 
INNER JOIN (
  SELECT scored_keyword.scored_page_id, 
    AVG(scored_keyword.score), 
    COUNT(scored_keyword.score), 
    AVG(scored_keyword.score) + (COUNT(scored_keyword.score) * .3) 
  FROM scored_keyword 
  WHERE (scored_keyword.user_id = 1000) AND (scored_keyword.keyword_id = 1329) 
  GROUP BY scored_keyword.scored_page_id 
  ORDER BY AVG(scored_keyword.score) + (COUNT(scored_keyword.score) * .3) DESC
) aggKeywordQuery ON  (scored_page.id = aggKeywordQuery.scored_page_id) 
ORDER BY aggKeywordQuery.scored_page_id DESC

When I change the .slice to include aggKeywordQuery[aggScoreColumn],

            .slice(ScoredPageTable.id, aggKeywordQuery[ScoredKeywordTable.scoredPageId], aggKeywordQuery[aggScoreColumn])

I get the error:

java.lang.IllegalStateException: Field not found in original table fields
    at org.jetbrains.exposed.sql.QueryAlias.get(Alias.kt:83)

What I really want to do is change the .orderby to

            .orderBy(aggKeywordQuery[aggKeywordQuery[aggScoreColumn]], SortOrder.DESC)

but I get the same error.

How can I add the columns from the join subquery to the result columns?

Answer 1

I managed to get this to work by using .alias.

        val aggScoreColumn = object : Function<Int?>(IntegerColumnType()) {
                override fun toQueryBuilder(queryBuilder: QueryBuilder) {
                    queryBuilder {
                        append("(${ScoredKeywordTable.score.avg()} + (${ScoredKeywordTable.score.count()} * .3))")
                    }
                }
--->        }.alias("aggScoreColumn")

        val scoreAvg = ScoredKeywordTable.score.avg().alias("scoreAvg")

        val aggKeywordQuery = ScoredKeywordTable
            .slice(
                ScoredKeywordTable.scoredPageId,
                scoreAvg,
                ScoredKeywordTable.score.count(),
--->            aggScoreColumn
            )
            .select {
                ScoredKeywordTable.userId eq userId and
                        (ScoredKeywordTable.keywordId inList keywordIds)
            }
            .groupBy(ScoredKeywordTable.scoredPageId)
            .orderBy(aggScoreColumn, SortOrder.DESC)
            .alias("aggKeywordQuery")

        val result = Join(ScoredPageTable)
            .join(aggKeywordQuery, JoinType.INNER,
                additionalConstraint = { ScoredPageTable.id eq aggKeywordQuery[ScoredKeywordTable.scoredPageId] })
// Slice no logger needed
//            .slice(ScoredPageTable.columns + aggKeywordQuery[ScoredKeywordTable.scoredPageId] + aggKeywordQuery[scoreAvg] + aggKeywordQuery[aggScoreColumn])
            .selectAll()
            .orderBy(aggKeywordQuery[aggScoreColumn], SortOrder.DESC)
            .toList()