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

Official F.A.Q. states that the only way to have subqueries is via alias for inner query:

val orderCount = Order.customerId.count().alias("OrderCount")
val subQuery = Order.slice(Order.customerId, orderCount)
                    .selectAll()
                    .groupBy(Order.customerId)
                    .alias("subQuery")
val query = Join(Customer)
                    .join(subQuery, JoinType.LEFT, subQuery[Order.customerId], Customer.id)
                    .slice(Customer.firstName, Customer.lastName, subQuery[orderCount])
                    .selectAll()

But maybe you don't need subqueries here? This one generates a bit different SQL-query with almost same query result (in contrast with the previous one, if there were no orders for customer, it will return 0 instead of null):

val query = Customer
            .leftJoin(Order, { Customer.id }, { Order.customerId })
            .slice(Customer.firstName, Customer.lastName, Order.id.count().alias("OrderCount"))
            .selectAll()
            .groupBy(Customer.id)

Generated SQL:

SELECT CUSTOMER."firstName",
       CUSTOMER."lastName",
       COUNT("ORDER".ID) OrderCount
FROM CUSTOMER
LEFT JOIN "ORDER" ON CUSTOMER.ID = "ORDER"."customerId"
GROUP BY CUSTOMER.ID

Answer 2

Thanks, that worked, although the first solution is hard to understand and some textual explanation of the logic applied would be appreciated.

I managed to extract data from it:

assertEquals("Manuel", query.andWhere { subQuery[orderCount] eq intLiteral(2) }.first()[Customer.firstName])

but I'm unable to extract data from the 2nd solution. Here is what I did:

val orderCount = Order.id.count().alias("OrderCount")

val query = Customer
    .leftJoin(Order, { Customer.id }, { Order.customerId })
    .slice(Customer.firstName, Customer.lastName, orderCount)
    .selectAll()
    .groupBy(Customer.id)

assertEquals("Manuel", query.andWhere { orderCount eq intLiteral(2) }.first()[Customer.firstName])

Answer 3

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])
}

Answer 4

I have found this other way, using the abstract Function class and creating an internal Query. You can check this post where it is explained better

https://medium.com/@jassielcastro/exposed-in-action-customizing-results-with-subqueries-in-ktor-e0eda69a7b31

open class SubCountQueryFunction<T : Table>(
    private val table: Alias<T>,
    private val count: String = "*",
    private val where: SqlExpressionBuilder.() -> Op<Boolean>
) : Function<Long>(LongColumnType()) {

    /**
     * Return example: 
     * (SELECT COUNT(*) FROM Table as TableAlias WHERE TableAlias.id = 1)
     */
    override fun toQueryBuilder(queryBuilder: QueryBuilder): Unit = queryBuilder {
        append('(')
        append("SELECT COUNT($count) FROM ${table.delegate.tableName} as ${table.alias} ")
        append("WHERE ${SqlExpressionBuilder.where()}")
        append(')')
    }
}