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?

score 4 · Answer 1 · answered Jan 04 '22 at 11:19

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

score 3 · Accepted Answer · answered Oct 03 '20 at 09:50

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

trimtosize · Answer 3 · 2020-10-03T18:58:51.307

0

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

edited Oct 03 '20 at 18:58

answered Oct 03 '20 at 12:46

trimtosize

213
2
10

When you work with `group by` in query you have to use `having` instead of `where` to filter results after grouping. You could read more [here](https://www.w3schools.com/sql/sql_having.asp). In Exposed it will looks like: `query.having { orderCount eq 2 }`. But keep in mind that `andWhere` and `having` mutates Query instance state. – Tapac Oct 04 '20 at 13:51
Thank you, but for it to work I had to move `alias("OrderCount")` from the variable `orderCount` to the place where it was used, i.e. in the `slice` function. @Tapac, now, considering the previous two examples, I got totally confused on how to properly use aliases. – trimtosize Oct 04 '20 at 16:11
To "extact data" from Query result you may use pure Kotlin: query.first { it[orderCount] == 2L }[Customer.firstName] – Михаил Нафталь Oct 04 '20 at 19:20
`andWhere` as well as `having` are not "extracting data" from query result, but keep constructing SQL-query. The `having` clause was added to SQL because the `where` clause could not be used with aggregate functions. Columns aliases in `having` clause can't be used too (because clause is related to aggregate function, not column) - this is a restriction of SQL, not Exposed. But generally you may use `where` in query when you have `group by` clause (or both `where` and `having`). See https://www.w3schools.com/sql/sql_having.asp – Михаил Нафталь Oct 04 '20 at 19:37
To convert ExpressionAlias back to Expression use its `delegate` field: `assertEquals("Manuel", query.having { orderCount.delegate eq intLiteral(2) }.first()[Customer.firstName])` – Михаил Нафталь Oct 04 '20 at 19:46

Handling subquery in a Kotlin Exposed framework

3 Answers3

SQL

How to get it in Exposed