Exposed: innerJoin table twice optReference issue

[spydevil]

I have the same problem: https://github.com/JetBrains/Exposed/issues/177

But my table is not IntIdTable. So I can't use optReference. So how I can solve this problem without using IntIdTable?

[Tapac]

Could you share your mapping and query which causes the problem?

[spydevil]

object Table1 : Table("Table") {
    val id = long("id").autoIncrement().primaryKey()
    val person_1 = reference("person1", UserTable.id)
    val person_2 = reference("person2", UserTable.id)
}

object UserTable : Table("User") {
    val id = integer("user_id").primaryKey()
    val password = varchar("password", 50)
    val name = varchar("name", 50)
}

(Table1 innerJoin UserTable).selectAll()

[Tapac]

Table1.innerJoin(UserTable, {Table1.person_1}, {UserTable.id}).selectAll()

[spydevil]

_It's not a real example I'm just trying to learn exposed library_

object Table1 : Table("Table") {
    val id = long("id").primaryKey()
    val person_1 = reference("person1", UserTable.id)
    val person_2 = reference("person2", UserTable.id)
    val person3 = reference("person3", UserTable.id)
    val person_4 = reference("person2", UserTable.id)
    val country = reference("country", Country.id)
}

object UserTable : Table("User") {
    val id = integer("user_id").primaryKey()
    val password = varchar("password", 50)
    val name = varchar("name", 50)
}

object Country : Table("Country") {
    val id = long("id").primaryKey()
    val name = varchar("name", 250)
}

Table1
            .innerJoin(UserTable.alias("u1"), { Table1.person_1 }, { UserTable.id })
            .innerJoin(UserTable.alias("u2"), { Table1.person_2 }, { UserTable.id })
            .innerJoin(UserTable.alias("u3"), { Table1.person_3 }, { UserTable.id })
            .innerJoin(UserTable.alias("u4"), { Table1.person_4 }, { UserTable.id })
        .innerJoin(Usertable.alias("u5"), { Table1.country }, {Country.id })
        .selectAll();

I get an error : com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier \"User.user_id\" could not be bound.

Should I use optReference in my table?

[Tapac]

I updated "Alias" section of a wiki, please look there for the example.

[spydevil]

_Thank you for your interest_ 👏
I try to run my code for hours in different varieties but I always got an error 😨

object MemberTable : Table("Member") {
    val id = long("id").primaryKey().autoIncrement()
    val userId = reference("user_id", UserTable.id)
    val roleId = reference("role_id", RoleTable.id)
    val givingBy = reference("giving_by", UserTable.id)
    val givingAt = datetime("giving_at")
}

object UserTable : Table("User") {
    val id = integer("id").primaryKey()
    val name = varchar("name", 50)
}

object RoleTable : Table("Role") {
    val id = integer("id").primaryKey()
    val name = varchar("name", 50)
    val groupId = reference("group_id", RoleGroupTable.id)
}

object RoleGroupTable : Table("RoleGroup") {
    val id = integer("id").primaryKey()
    val name = varchar("name", 50)
}

private val tMember = MemberTable.alias("m")
private val tUsers1 = UserTable.alias("u1")
private val tUsers2 = UserTable.alias("u2")
private val tRole = RoleTable.alias("r")
private val tRoleG = RoleGroupTable.alias("g")
private val TABLE_WITH_JOIN_FOR_ROLE = (
        tMember.innerJoin(tUsers1, { tMember[MemberTable.userId] }, { tUsers1[UserTable.id] }) innerJoin
                tMember.innerJoin(tUsers2, { tMember[MemberTable.givingBy] }, { tUsers2[UserTable.id] }) innerJoin
                tMember.innerJoin(tRole, { tMember[MemberTable.roleId] }, { tRole[RoleTable.id] }) innerJoin
                tRole.innerJoin(tRoleG, { tRole[RoleTable.groupId] }, { tRoleG[RoleGroupTable.id] })
        )
        .slice(tMember[MemberTable.id], tMember[MemberTable.givingBy], tMember[MemberTable.givingAt], tRole[RoleTable.name], tRoleG[RoleGroupTable.name])

Error: Cannot join with org.jetbrains.exposed.sql.Join@133a7e53 as there is no matching primary key/ foreign key pair and constraint missing

But the underlying sql code is working

SELECT m.id, m.giving_by, m.giving_at, u1.name, r.name, g.name
from member as m
       INNER JOIN users u1 on m.user_id = u1.id
       INNER JOIN users u2 on m.giving_by = u2.id
       INNER JOIN role r on m.role_id = r.id
       INNER JOIN role_group g on r.group_id = g.id

[Tapac]

MemberTable
    .innerJoin(tUsers1, { MemberTable.userId }, { tUsers1[UserTable.id] })
    .innerJoin(tUsers2, { MemberTable.givingBy }, { tUsers2[UserTable.id] })
    .innerJoin(RoleTable)
    .innerJoin(RoleGroupTable)
    .slice(MemberTable.id, MemberTable.givingBy, MemberTable.givingAt, tUsers1[UserTable.name], RoleTable.name, RoleGroupTable.name)
    .selectAll()

gives such sql:

SELECT "MEMBER".ID, "MEMBER".GIVING_BY, "MEMBER".GIVING_AT, U1.NAME, ROLE.NAME, ROLEGROUP.NAME
FROM "MEMBER"
INNER JOIN "USER" u1 ON "MEMBER".USER_ID = U1.ID
INNER JOIN "USER" u2 ON "MEMBER".GIVING_BY = U2.ID
INNER JOIN ROLE ON ROLE.ID = "MEMBER".ROLE_ID
INNER JOIN ROLEGROUP ON ROLEGROUP.ID = ROLE.GROUP_ID;

Looks the same as your working example.