Joining a last activity record about ktorm HOT 5 CLOSED

val employees = useConnection { conn ->
    val sql = """
        select emp.id as emp_id, emp.name as emp_name, act.id as act_id, act.desc as act_desc, act.created_at as act_time
        from employee emp left join (
            select a.id, a.employee_id, a.desc, a.created_at
            from activity a
            where a.id = (
                select max(b.id)
                from activity b
                where b.employee_id = a.employee_id
            )
        ) act on emp.id = act.employee_id;
    """.trimIndent()

    conn.prepareStatement(sql).use { statement ->
        statement.executeQuery().use { rs ->
            rs.iterable().map { row ->
                Employee {
                    id = row.getLong("emp_id")
                    name = row.getString("emp_name")
                    latestActivity = Activity {
                        id = row.getLong("act_id")
                        desc = row.getString("act_desc")
                        createdAt = row.getTimestamp("act_time")?.toLocalDateTime()
                    }
                }
            }
        }
    }
}

// select ... from employee
val employees = Employees.findAll()

val employeeIds = employees.map { it.id }

// select activity.employee_id, max(activity.id)
// from activity
// where activity.employee_id in (?, ?, ...)
// group by activity.employee_id
val latestActivityIds = Activities.asSequence()
    .filter { it.employeeId inList employeeIds }
    .groupingBy { it.employeeId }
    .eachMaxBy { it.id }

// select ... from activity where activity.id in (?, ?, ...)
val activities = Activities.findMapByIds(latestActivityIds.values.filterNotNull())

for (employee in employees) {
    val activityId = latestActivityIds[employee.id] ?: continue
    employee.latestActivity = activities[activityId]
}