Is there a way to do a join with a max condition? ...

# exposed

[Allan Wang]

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

[tapac]

Yes, you can use wrapAsExpression() function and write something like:

address.cityId eq wrapAsExpression<Int>(cities.slice(cities.id.max()).selectAll())

[Allan Wang]

is that the full expression? Is there no need for a table join beforehand or does that represent the join?

[tapac]

It's just an example how you can use wrapAsExpression in a select condition.

[Allan Wang]

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:

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

(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

PrinterTable.id eq wrapAsExpression(PrinterStatusTable.slice(PrinterStatusTable.date.max()).selectAll())

But I don’t think that makes sense

(@tapac)