I'm having trouble accessing optional columns from multiple left joins. Calling `ResultRow.hasValue...
a

Andrew O'Hara

over 2 years ago
I'm having trouble accessing optional columns from multiple left joins. Calling
ResultRow.hasValue
for the joined columns always returns
true
, which makes it difficult for me to tell which optional columns are actually present. Below is a contrived example, followed by a passing and a failing test. It should illustrate my problem better than my explanation. Am I doing this completely wrong?
object BaseUser: IntIdTable("base_users")
object SuperUser: IntIdTable("super_users") {
    val baseId = reference("base_id", BaseUser)
    val name = varchar("name", 128)
}
object NormalUser: IntIdTable("normal_users") {
    val baseId = reference("base_id", BaseUser)
    val name = varchar("name", 128)
}

fun Transaction.getUserName(baseId: EntityID<Int>): String? {
    val row = BaseUser
        .leftJoin(SuperUser, {SuperUser.baseId}, {BaseUser.id})
        .leftJoin(NormalUser, {NormalUser.baseId}, {BaseUser.id})
        .slice(NormalUser.name, SuperUser.name)
        .select { BaseUser.id eq baseId }
        .firstOrNull()

    return when {
        row == null -> null
        row.hasValue(NormalUser.name) -> row[NormalUser.name]  // Still enters here even for SuperUser
        row.hasValue(SuperUser.name) -> row[SuperUser.name]
        else -> null
    }
}

@Test
fun `navigate subtype relationship`() {
    transaction {
        SchemaUtils.create(BaseUser, SuperUser, NormalUser)

        val baseUserId1 = BaseUser.insertAndGetId {}
        val baseUserId2 = BaseUser.insertAndGetId {}

        NormalUser.insert {
            it[baseId] = baseUserId1
            it[name] = "normaldude"
        }
        SuperUser.insert {
            it[baseId] = baseUserId2
            it[name] = "superdude"
        }

        getUserName(baseUserId1) shouldBe "normaldude" // PASS
        getUserName(baseUserId2) shouldBe "superdude" // FAIL; was actually null
    }
}