Question about modeling 1-N relationship with mapper

[pjagielski]

I wanted to model simple domain with 1-N relationship. I have this query working in core:

select * from scores s join users u where s.created_at > '${date.toString("yyyy-MM-dd")}' order by s.score desc limit 20

But don't know how to model it with mapper. With Exposed it's as simple as this:

object Users : Table() {
    val id    = integer("id").primaryKey()
    val login = text("login")
}

object Scores : Table() {
    val score       = integer("score")
    val createdAt   = date("created_at")
    val userId      = integer("user_id") references Users.id
}

   override fun findAll(): List<Score> {
        return (Scores innerJoin Users).select { Scores.createdAt.greater(DateTime.now().minusDays(2)) }
                .orderBy(Scores.score, false)
                .limit(20)
                .map { r -> Score(r[Scores.score], r[Users.login]) }
}

I got something like this but, obviously, login is set to empty string:

class User(val id: Int, val login: String = "")

class PersistentScore(val id: Int, val user: User, score: Int) : Score(score, user.login)

object userConverter : SimpleConverter<User>(
        { row, c -> User(row.int(c)) },
        User::id
)

val tableConfig = TableConfiguration(
        defaults = standardDefaults + reifiedValue(User(0)),
        converters = standardConverters + reifiedConverter(userConverter)
)

object userTable: Table<User, Int>("users", tableConfig) {
    val Id          by col(User::id, id = true)
    val LoginVal    by col(User::login)

    override fun create(value: Value<User>): User =
        User(value of Id, value of LoginVal)

    override fun idColumns(id: Int) = setOf(Id of id)
}

object scoreTable : Table<PersistentScore, Int>("scores", tableConfig) {
    val Id          by col(PersistentScore::id, id = true)
    val ScoreVal    by col(PersistentScore::score, name = "score")
    val UserId      by col(PersistentScore::user)

    override fun create(value: Value<PersistentScore>): PersistentScore =
            PersistentScore(value of Id, value of UserId, value of ScoreVal)

    override fun idColumns(id: Int) = setOf(Id of id)
}
   fun findScores(): List<PersistentScore> {
        val name = "findScores"
        val sql = sql(name) { "select * from ${table.name} where created_at > :date order by score desc limit 10" }
        return session.select(sql, mapOf("date" to DateTime.now().minusDays(2).toString("yyyy-MM-dd")), options(name), table.rowMapper())
    }

Any help?

[andrewoma]

Hi Piotr,

Have a look at https://github.com/andrewoma/kwery/blob/master/mapper/src/test/kotlin/com/github/andrewoma/kwery/mappertest/example/FilmDao.kt#L91

This is an example of fetching a related entity with joins using mappers.

Cheers,
Andrew

[pjagielski]

Ok, thanks