Handling subquery in a Kotlin Exposed framework

Question

Is Exposed 0.27.1 capable to translate the following SQL statement?

SELECT FirstName, LastName,
   (SELECT COUNT(O.Id)
    FROM "Order" O
    WHERE O.CustomerId = C.Id) AS OrderCount
FROM Customer C;

Here is what I tried but unfortunately the subquery works independently to the rest of the query.

val query = Customer
    .leftJoin(Order, { Customer.id }, { Order.customerId })
    .slice(
            Customer.firstName,
            Customer.lastName,
            intLiteral(Order
                    .innerJoin(Customer, { Order.customerId }, { Customer.id })
                    .slice(Order.id.count())
                    .select { Order.customerId eq Customer.id }
                    .first()[Order.id.count()].toInt())//.alias("OrderCount")
    )
    .selectAll()

Besides, if that would be possible then how I could use the alias to fetch result from the ResultRow? Following this example it seems that the solution would be to store the entire subquery with an alias() method call in a single variable but that will look ugly. Is there any better way to do that?

Answer 1

Previous answer don't generate subquery in select clause. The way that describe below allowed it. This is tested on Exposed 0.36.2.

SQL

Next example executes SQL:

SELECT "groups".id, "groups".name,
       (SELECT COUNT(group_members.user_id) FROM group_members 
         WHERE group_members.group_id = "groups".id) members_count
FROM "groups";

How to get it in Exposed

First, we need a wrapper that convert AliasQuery to Expression:

class SubQueryExpression<T>(private val aliasQuery : QueryAlias) : Expression<T>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        aliasQuery.describe(TransactionManager.current(), queryBuilder)
    }
}

Create subquery:

val membersCount = GroupMembersTable.userId.count()
val subSelect  = GroupMembersTable
                .slice(membersCount)
                .select { GroupMembersTable.groupId eq GroupsTable.id}
val subQuery: QueryAlias = subSelect.alias("members_count")

Wrap subquery to expression:

val membersCountExp = SubQueryExpression<Long>(subQuery)

Make full query:

val q = GroupsTable
         .slice(GroupsTable.fields + membersCountExp)
         .selectAll()

Execute and read calculated value:

q.forEach {
 println(it[membersCountExp])
}