Retrieve Joined SubQuery Count Column

Question

I'm running a subquery to count the number of matching keywords and then joining that with a RankedPage table. The problem is that the count field doesn't get converted into a column.

        val keywordCount = KeywordTable.word.count()
        val keywordCountQuery = PageKeywordTable.join(KeywordTable, JoinType.INNER, PageKeywordTable.keywordId, KeywordTable.id)
            .slice(PageKeywordTable.rankedPageId, keywordCount.alias("keyword_count"))
            .select {
                KeywordTable.word inList listOf("foo", "bar")
            }.groupBy(PageKeywordTable.rankedPageId)
            .having { keywordCount greaterEq  1 }
        val subQuery = keywordCountQuery.alias("subQuery")

        val pages = db.transaction {
            addLogger(StdOutSqlLogger)
            val columns = RankedPageTable.columns + subQuery.columns
            Join(RankedPageTable).join(subQuery, JoinType.INNER, RankedPageTable.id, subQuery[PageKeywordTable.rankedPageId])
                .slice(columns)
                .selectAll()
                .toList()
        }

If I look at the SQL query, I see the subQuery.page_keyword.ranked_page_id in the select section, but don't see the count column. I have tried adding subQuery[keywordCount], but get an error saying column doesn't exist.

The subQuery SQL excerpt:

... SELECT page_keyword.ranked_page_id, COUNT(keyword.word) keyword_count ...

How do I get the RankePageTable join to have a column subQuery.keyword_count?

Answer 1

I have found a workaround. I'm able to create an expression that adds the correct SQL.

        val subQueryCount = object: Expression<Int>() {
            override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
                append("subQuery.keyword_count")
            }
        }

            val columns = RankedPageTable.columns + subQueryCount
            Join(RankedPageTable).join(subQuery, JoinType.INNER, RankedPageTable.id, subQuery[PageKeywordTable.rankedPageId])
                .slice(columns)
                .selectAll()
                .toList()

I then can pass the subQueryCount to the result items to get the count.

Is there a better way to accomplish the same thing?