Exposed: How can i use innerJoin without foreign key

[luckyxiaomo]

java.lang.IllegalStateException: Cannot join with Users@4e39de8 as there is no matching primary key/ foreign key pair and constraint missing

first, i am sorry for my poor English.
my issue is like the https://github.com/JetBrains/Exposed/issues/193

if i want use innerJoin, then i have to use references in object like this image

That's fine, but if i use this to create table with the ‘SchemaUtils.create’ function, it will create a foreign key in my postgresql. I do not want any foreign keys in my postgresql, but i need innerJoin.

I have two suggests :
1: give a function to delete all foreign keys
2: use SchemaUtils.create(Tables), i can choose not create foreign keys in postgresql

summarize:
I don't need any physical foreign keys, but I need logical foreign keys

I hope you can reply

[Tapac]

You can use extended version of join syntax:
FirstTable.join(SecondTable, JoinType.INNER, FirstTable.id, SecondTable.otherColumn)

[yuri-li]

We can communicate in Chinese.
中文交流也可以的呀...

The reason for not using foreign keys is because of the requirements of business scenarios.
不使用外键的原因，是因为业务场景的要求。

The company I serve is an Internet company, whose business is constantly changing.
我服务的公司是互联网行业，业务需求一直在变。

so, table structure and data need to be constantly modified. but, DBA doesn't care.
表结构、数据，需要持续更改。而DBA对业务不敏感。

When there are foreign keys, the repair of table structure and data must be performed in sequence.
有了外键，修复表结构、数据的时候，必须按顺序执行。

When a table has hundreds of millions of data, how much is the cost of trial mistake?
你可以想想，当一个表有上亿条数据的时候，试错的成本有多大？

After all, even if you have tested many times in the test environment, you may get stuck when you go online.
毕竟，就算你在测试环境测了N遍，上线的时候也可能卡死了

so, Do not use foreign keys
所以，不要使用外键。

As for join, let me give you a complete example:
至于join，我给你一个完整的例子：

import io.kotlintest.extensions.TestListener
import io.kotlintest.shouldBe
import io.kotlintest.specs.StringSpec
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.dao.IntIdTable
import org.jetbrains.exposed.dao.UUIDTable

object ConnListener : TestListener {
    override fun beforeSpec(spec: Spec) {
        Database.connect(
            url = "jdbc:mysql://192.168.128.128:3306/json?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false",
            driver = "com.mysql.cj.jdbc.Driver",
            user = "root", password = "123456"
        )
    }
}

object Users : IntIdTable("t_user") {
    val name = varchar("c_name", length = 50)
    val sex = bool("sex").default(true)
    val birthday = date("birthday").defaultExpression(CurrentDateTime())
    val createTime = datetime("create_time").defaultExpression(CurrentDateTime())
}

object Scores : UUIDTable("t_score") {
    val userId = integer("user_id").nullable().index()
    val score = integer("score").nullable()
}

data class UserScore(val id: Int, val name: String, val score: Int)

class JoinSpec : StringSpec({
    "测试join"{
        transaction {
            addLogger(StdOutSqlLogger)
            SchemaUtils.drop(Users, Scores)
            SchemaUtils.create(Users, Scores)

            listOf("yuri", "peter", "perry").forEach { userName ->
                Users.insert {
                    it[name] = userName
                }
            }

            listOf(100, 80, 90).forEachIndexed { index: Int, userScore: Int ->
                Scores.insert {
                    it[userId] = index + 1
                    it[score] = userScore
                }
            }

            val userScores =
                Users.join(Scores, JoinType.INNER, additionalConstraint = { Users.id eq Scores.userId })
                    .slice(Users.id, Users.name, Scores.score)
                    .selectAll()
                    .orderBy(Scores.score).map {
                        UserScore(it[Users.id].value, it[Users.name], it[Scores.score]!!)
                    }
            userScores.size.shouldBe(3)
            userScores.get(0).name.shouldBe("peter")
        }
    }
}) {
    override fun listeners(): List<TestListener> = listOf(ConnListener)
}

[luckyxiaomo]

You can use extended version of join syntax:
FirstTable.join(SecondTable, JoinType.INNER, FirstTable.id, SecondTable.otherColumn)

Thank you

[luckyxiaomo]

We can communicate in Chinese.
中文交流也可以的呀...

The reason for not using foreign keys is because of the requirements of business scenarios.
不使用外键的原因，是因为业务场景的要求。

The company I serve is an Internet company, whose business is constantly changing.
我服务的公司是互联网行业，业务需求一直在变。

so, table structure and data need to be constantly modified. but, DBA doesn't care.
表结构、数据，需要持续更改。而DBA对业务不敏感。

When there are foreign keys, the repair of table structure and data must be performed in sequence.
有了外键，修复表结构、数据的时候，必须按顺序执行。

When a table has hundreds of millions of data, how much is the cost of trial mistake?
你可以想想，当一个表有上亿条数据的时候，试错的成本有多大？

After all, even if you have tested many times in the test environment, you may get stuck when you go online.
毕竟，就算你在测试环境测了N遍，上线的时候也可能卡死了

so, Do not use foreign keys
所以，不要使用外键。

As for join, let me give you a complete example:
至于join，我给你一个完整的例子：

import io.kotlintest.extensions.TestListener
import io.kotlintest.shouldBe
import io.kotlintest.specs.StringSpec
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.dao.IntIdTable
import org.jetbrains.exposed.dao.UUIDTable

object ConnListener : TestListener {
    override fun beforeSpec(spec: Spec) {
        Database.connect(
            url = "jdbc:mysql://192.168.128.128:3306/json?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false",
            driver = "com.mysql.cj.jdbc.Driver",
            user = "root", password = "123456"
        )
    }
}

object Users : IntIdTable("t_user") {
    val name = varchar("c_name", length = 50)
    val sex = bool("sex").default(true)
    val birthday = date("birthday").defaultExpression(CurrentDateTime())
    val createTime = datetime("create_time").defaultExpression(CurrentDateTime())
}

object Scores : UUIDTable("t_score") {
    val userId = integer("user_id").nullable()
    val score = integer("score").nullable()
}

data class UserScore(val id: Int, val name: String, val score: Int)

class JoinSpec : StringSpec({
    "测试join"{
        transaction {
            addLogger(StdOutSqlLogger)
            SchemaUtils.drop(Users, Scores)
            SchemaUtils.create(Users, Scores)

            listOf("yuri", "peter", "perry").forEach { userName ->
                Users.insert {
                    it[name] = userName
                }
            }

            listOf(100, 80, 90).forEachIndexed { index: Int, userScore: Int ->
                Scores.insert {
                    it[userId] = index + 1
                    it[score] = userScore
                }
            }

            val userScores =
                Users.join(Scores, JoinType.INNER, additionalConstraint = { Users.id eq Scores.userId })
                    .slice(Users.id, Users.name, Scores.score)
                    .selectAll()
                    .orderBy(Scores.score).map {
                        UserScore(it[Users.id].value, it[Users.name], it[Scores.score]!!)
                    }
            userScores.size.shouldBe(3)
            userScores.get(0).name.shouldBe("peter")
        }
    }
}) {
    override fun listeners(): List<TestListener> = listOf(ConnListener)
}

谢谢你，你学kotlin 多久了啊，我感觉比java好用多了

[Tapac]

@yuri-li , how do you keep your data in a consistent state without a reference check? Do you speed up your joins with indexes?

[yuri-li]

@yuri-li , how do you keep your data in a consistent state without a reference check? Do you speed up your joins with indexes?

Thank you. Your opinion is very good. Indexes still need to be created. So, I revised demo.