kotlin - 如何避免 kotlin Exposed 上的 N+1 查询问题。 (当通过 DAO 的 Reference.id.value 字段获取值时)

Question

我在 Kotlin JVM 项目上使用 Exposed 作为 O/R 映射器。
(版本:0.17.6)

当我从 DAO API(由 Exposed 的 reference 方法定义的列)获取外键列的值时，我遇到了 N+1 查询问题。

我以某种方式用变通代码解决了这个问题，但有人知道正确的解决方案吗？

这是情况。

有两个表(用户，user_addresses)，一个表(user_addresses 表)在另一个表(用户表)上有一个外键引用。

object Users : LongIdTable("users") {
    val name = varchar("name", 30)
}
class User(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<User>(Users)

    var name by Users.name
}

object UserAddresses : LongIdTable("user_addresses") {
    val user = reference("user_id", Users)
    val address = varchar("address", 30)
}
class UserAddress(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<UserAddress>(UserAddresses)

    var user by User.referencedOn(UserAddresses.user)
    var address by UserAddresses.address

    fun toRow(): UserAddressRow {    
        return UserAddressRow(
            id.value,
            user.id.value,
            address
        )
    }
}

data class UserAddressRow(val id: Long, val userId: Long, val address: String)

当我尝试获取 List<UserAddressRow> 时，出现 N+1 查询问题。
我发现当我调用 Reference.getValue 时，SELECT 查询在下面的点上执行。
https://github.com/JetBrains/Exposed/blob/master/exposed-dao/src/main/kotlin/org/jetbrains/exposed/dao/Entity.kt#L129

@Test
fun test() {
    transaction {
        UserAddress.all().map {
            it.toRow()
        }
    }
}    

测试数据

INSERT INTO users (id, name) VALUES (1, 'A');
INSERT INTO users (id, name) VALUES (2, 'B');
INSERT INTO user_addresses (user_id, address) VALUES (1, 'X');
INSERT INTO user_addresses (user_id, address) VALUES (1, 'Y');
INSERT INTO user_addresses (user_id, address) VALUES (2, 'Z');

SQL 查询日志(暴露的调试日志)

[] 2019-11-22 10:41:55.656 [main] DEBUG [cid- uid--] Exposed - SELECT user_addresses.id, user_addresses.user_id, user_addresses.address FROM user_addresses
[] 2019-11-22 10:41:55.668 [main] DEBUG [cid- uid--] Exposed - SELECT users.id, users."name" FROM users WHERE users.id = 1
[] 2019-11-22 10:41:55.670 [main] DEBUG [cid- uid--] Exposed - SELECT users.id, users."name" FROM users WHERE users.id = 2

如果我使用 readValue 而不是 user.id ，问题就解决了。
我不确定这个解决方法是否正确。请帮我。

   fun toRow(): UserAddressRow {    
        return UserAddressRow(
            id.value,
            readValues[UserAddressTable.user].value,
            address
        )
    }

Answer 1

我注意到下面的简单模式会更好。

//Define userId column in DAO (UserAddress class), and use it
class UserAddress(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<UserAddress>(UserAddresses)

    var user by User.referencedOn(UserAddresses.user)
    var userId by UserAddresses.user
    var address by UserAddresses.address

    fun toRow(): UserAddressRow {
        return UserAddressRow(
            id.value,
            userId.value,
            address
        )
    }
}

//There is only one SQL statement executed
//SELECT user_addresses.id, user_addresses.user_id, user_addresses.address FROM user_addresses