Exposed: innerJoin table twice

[coolemza]

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.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 use it in general? i dont understand how to set onColumn/otherColumn parameters

[Tapac]

Please try this code:

object Users : Table() {
    val id = varchar("id", 10).primaryKey()
    val name = varchar("name", length = 50)
    val residentialCityId = optReference("resid_city_id", Cities)
    val bornCityId = optReference("born_city_id", Cities)
}

object Cities : IntIdTable() {
    val name = varchar("name", 50) // Column
}
fun test() {
    val userTable1 = Users.alias("u1")
    val userTable2 = Users.alias("u2")

    Cities
            .innerJoin(userTable1, {Cities.id}, {userTable1[Users.residentialCityId]})
            .innerJoin(userTable2, {Cities.id}, {userTable2[Users.bornCityId]})
            .selectAll()
}

I've changed Cities table from base Table to IntIdTable, it helps to replace (integer(“city_id”) references Cities.id).nullable() with simple optReference("resid_city_id", Cities).

Let me explain, why this innerJoin take 2nd and 3d parameters as lambda. Receiver of first lambda is first table in join, and second table in second lambda. It allows to write Cities.innerJoin(Users, {id}, {bornCityId}) and be sure that id is column from Cities and bornCityId is from Users.

[coolemza]

Yes, it worked

[EVOSandru6]

Hello, how to make delete in suсh related style by condition?