Hey, I have the following db schema (see pic). Now...

# exposed

[Marc]

Hey, I have the following db schema (see pic). Now I want to get all shifts from a roster with their corresponding assignees. Currently I do it like that:

suspend fun getRosterAndShiftsById(rosterId: String): Result<Pair<RosterModel, List<ShiftModel>>, DataError.CrudError> {
    println("Searching roster...")
    val roster = rosterRepository.findById(rosterId) ?: return Result.Error(DataError.CrudError.NOT_FOUND)
    println("Found roster!")
    println("Searching shifts...")
    val shifts = shiftRepository.findAllBy { Shifts.rosterId eq rosterId }
    println("Found shifts!")
    return Result.Success(roster.toModel() to shifts.map { it.toModel() })
}

My shiftRepository#findAllBy looks like that:

override suspend fun findAllBy(filter: DaoPredicate) = newSuspendedTransaction(ctx) {
    addLogger(StdOutSqlLogger)
    val result = Dao.find(filter).toList()
    result.forEach { addReferences(it) }
    result
}

private fun addReferences(dao: ShiftDao) {
    dao.cachedAssignees = dao.assignees.toList()
    dao.cachedClient = dao.client
}

With the addReferences function I want to ensure, that I can get all the assignees in every place (especially outside of transactions) in order to display them on the roster-overview.
This works fine, however it's generating lots of requests to the server (see second picture), which causes very long loading times. I was wondering, if there is a better way to achieve the same goal without sending that much requests. 😅

[Marc]

Would it be smarter to just select all staff members at once and then join them on the client side?

[Oleg Babichev]

Hi, the Eager loading section in the documentation might be useful for you https://jetbrains.github.io/Exposed/deep-dive-into-dao.html#eager-loading

I made a simple version of your DB structure:

object Shifts : IntIdTable("Shifts") {
        val rosterId = integer("rosterId")
    }

    class Shift(id: EntityID<Int>) : IntEntity(id) {
        var rosterId by Shifts.rosterId

        var assignees by Staff via ShiftAssignees

        companion object : IntEntityClass<Shift>(Shifts)
    }

    object ShiftAssignees : Table("ShiftAssignees") {
        val shift = reference("shift", Shifts.id)
        val staff = reference("staff", Staffs.id)

        override val primaryKey = PrimaryKey(shift, staff)
    }

    object Staffs : IntIdTable("Staffs") {
        val name = varchar("name", length = 128)
    }

    class Staff(id: EntityID<Int>) : IntEntity(id) {
        var name by Staffs.name

        var shift by Shift via ShiftAssignees

        companion object : IntEntityClass<Staff>(Staffs)
    }

If I iterate assignees in the following way:

val allShiftsString = Shift
                .find { Shifts.rosterId eq rosterId }
                .joinToString(prefix = "<Shift ", postfix = ">", separator = ", ") { shift ->
                    shift.assignees.joinToString(", ") { it.name }
                }
            println(allShiftsString)

I get same problem and logs look like:

SQL: SELECT staffs.id, staffs."name", shiftassignees.staff, shiftassignees.shift FROM staffs INNER JOIN shiftassignees ON staffs.id = shiftassignees.staff WHERE shiftassignees.shift = 1
SQL: SELECT staffs.id, staffs."name", shiftassignees.staff, shiftassignees.shift FROM staffs INNER JOIN shiftassignees ON staffs.id = shiftassignees.staff WHERE shiftassignees.shift = 2
...

But if I add the call of with(Shift::assignees) it loads all the assignees at once:

val allShiftsString = Shift
                .find { Shifts.rosterId eq rosterId }
                .with(Shift::assignees)
                .joinToString(prefix = "<Shift ", postfix = ">", separator = ", ") { shift ->
                    shift.assignees.joinToString(", ") { it.name }
                }
            println(allShiftsString)

And the related logs:

SELECT staffs.id, staffs."name", shiftassignees.shift, shiftassignees.staff FROM staffs INNER JOIN shiftassignees ON shiftassignees.staff = staffs.id WHERE shiftassignees.shift IN (1, 2, 3, 4, 5)

[Marc]

It seems not to save this data in the dao thogh..

fun ShiftDao.toModel() = ShiftModel(
    date = date,
    startTime = startTime,
    endTime = endTime,
    text = text,
    isEvent = isEvent,
    //rosterLoader = { this.roster.toModel() },
    client = client?.toModel(),
    assignees = assignees.map { it.toModel() },
    id = id.value
)

suspend fun getRosterAndShiftsById(rosterId: String): Result<Pair<RosterModel, List<ShiftModel>>, DataError.CrudError> {
    println("Searching roster...")
    val roster = rosterRepository.findById(rosterId) ?: return Result.Error(DataError.CrudError.NOT_FOUND)
    println("Found roster!")
    println("Searching shifts...")
    val shifts = shiftRepository.findAllBy { Shifts.rosterId eq rosterId }
    println("Found shifts!")
    return Result.Success(roster.toModel() to shifts.map { it.toModel() })
}

[Oleg Babichev]

According to the error it looks like your function were executed outside of transaction. Have you wrapped your code into transaction block?

transaction {
  getRosterAndShiftsById("<id>")
}

[Marc]

No, I thought they were stored in the dao after accessing it the first time