静态编程语言中的子查询处理

Question

暴露0.27.1是否能够翻译以下SQL语句？

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

下面是我尝试的内容，但不幸的是，子查询独立于查询的其余部分工作。

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

此外，如果可能的话，那么如何使用别名从ResultRow获取结果？在这个示例之后，解决方案似乎是将整个子查询存储在单个变量中，并使用一个alias（）方法调用，但这看起来很难看。有没有更好的方法？

Answer 1

谢谢，这很有效，尽管第一个解决方案很难理解，如果您能对所应用的逻辑进行一些文本解释，我们将不胜感激。

我设法从中提取数据：

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

但是我无法从第二个解决方案中提取数据。这是我所做的：

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 2

官方F.A.Q.表示，进行子查询的唯一方法是通过alias进行内部查询：

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

但也许这里不需要子查询？这一次生成的SQL查询略有不同，查询结果几乎相同（与前一次相比，如果没有客户订单，它将返回0而不是null）：

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

生成的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 3

以前的答案不会在选择子句中生成子查询。下面描述的方式允许它。这是在暴露的0.36.2上测试的。

下一个示例执行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";

首先，我们需要一个将AliasQuery转换为表达式的包装器：

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

创建子查询：

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

将子查询包装到表达式：

val membersCountExp = SubQueryExpression<Long>(subQuery)

进行完整查询：

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

执行并读取计算值：

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