How to avoid N+1 query problem on kotlin Exposed. (When getting value by DAO's Reference.id.value field)

Question

I use Exposed as O/R Mapper on Kotlin JVM project. (version: 0.17.6)

I had trouble with the N+1 query problem when I got a foreign key column's value from DAO API (columns defined by Exposed's reference method).

I somehow solve the problem with a workaround code, but does anyone know about the correct solution?

Here is the situation.

There are two tables (users, user_addresses) and one table(user_addresses table) has a foreign key reference on the other(user table).

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)

When I tried to get List<UserAddressRow>, the N+1 query problem occurs. I found that when I call Reference.getValue, SELECT query is executed on below point. 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()
        }
    }
}    

TestData

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 Query Log (Exposed Debug Log)

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

If I use readValue instead of user.id, the problem is solved. I'm not sure this workaround is correct. Please help me.

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

Denounce

Please look at the documentation here github.com/JetBrains/Exposed/wiki/DAO#eager-loading – Egyptology

Thank you for your helpful advise!! It was a great learning. But actually in this case, some useless SQL is executed on using eager loading. Here is the detail. Eager-loading code: UserAddress.all().with(UserAddress::user). Executed useless SQL: SELECT users.id, users."name" FROM users WHERE users.id IN (1, 1, 2). – Denounce

There is no need to select from users table, so I want to execute only for querying user_addresses table. – Denounce

Answer 1

I noticed below simple pattern would work better.

//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

Denounce