Kotlin Exposed - How to use multiple tables innerJoin?

In my project i have table with multiple keys refers same table, like this:

object Users : Table() {
val id = varchar(“id”, 10).primaryKey()
val name = varchar(“name”, length = 50)
val residentialCityId = (integer(“city_id”) references Cities.id).nullable()
val bornCityId = (integer(“city_id”) references Cities.id).nullable()

object Cities : Table() {
val id = integer(“id”).autoIncrement().primaryKey() // Column
val name = varchar(“name”, 50) // Column

so i have to join same table 2 times, i need something like this:

Users.join(Cities.alias(“ResidentialCiti”), JoinType.INNER, Users.residentialCityId, Cities.id)
.join(Cities.alias(“BornCiti”), JoinType.INNER, Users.bornCityId, Cities.id).slice(…).selectAll()

this one is wrong, and i cant understand how to do it…
There is also generic in Table.kt:

fun <C1:ColumnSet, C2:ColumnSet> C1.innerJoin(otherTable: C2, onColumn: C1.() → Expression<>, otherColumn: C2.() → Expression<>) = join(otherTable, JoinType.INNER, onColumn(this), otherColumn(otherTable))

can it be used for this case and how to is it in general? i dont understand how to set onColumn/otherColumn parameters

thank you,

Solution here innerJoin table twice · Issue #177 · JetBrains/Exposed · GitHub

1 Like