https://kotlinlang.org logo
#exposed
Title
# exposed
a

Allan Wang

11/10/2018, 7:27 AM
Is there a way to do a join with a max condition? Like this example: https://stackoverflow.com/a/725164/4407321 A potential alternative if not is for me to store a reference id in the left table, but I'd like to avoid that
t

tapac

11/10/2018, 9:39 AM
Yes, you can use
wrapAsExpression()
function and write something like:
Copy code
address.cityId eq wrapAsExpression<Int>(cities.slice(cities.id.max()).selectAll())
a

Allan Wang

11/11/2018, 6:33 AM
is that the full expression? Is there no need for a table join beforehand or does that represent the join?
t

tapac

11/11/2018, 8:44 AM
It's just an example how you can use
wrapAsExpression
in a select condition.
a

Allan Wang

11/18/2018, 3:39 AM
Hello, sorry but I still can’t quite figure it out. Would you be able to help me find the full query for this problem? Given tables:
Copy code
object PrinterTable : IdTable<String>("printer") {
    override val id = varchar("id", ID_SIZE).entityId()
    val name = varchar("name", NAME_SIZE).uniqueIndex()
    val group = varchar("group", ID_SIZE)
}

object PrinterStatusTable : IdTable<String>("printer_status") {
    override val id = varchar("id", ID_SIZE).primaryKey(0).entityId().references(PrinterTable.id, ReferenceOption.CASCADE)
    val date = datetime("date").primaryKey(1).clientDefault(DateTime::now)
    val user = varchar("user", USER_SIZE)
    val flag = varchar("flag", FLAG_SIZE)
    val message = varchar("message", MESSAGE_SIZE)
}
I want to left join a printer to the latest printer status where the id matches. From what I see online, it feels like I should be approaching it with something like
Copy code
(PrinterTable leftJoin ***).select { PrinterTable.id eq PrinterStatusTable.id }
where * represents the status table with only the latest entry for every id. Following your example above, I can write something like
Copy code
PrinterTable.id eq wrapAsExpression(PrinterStatusTable.slice(PrinterStatusTable.date.max()).selectAll())
But I don’t think that makes sense (@tapac)