I am setting up some many-to-many relationships and have so far been using the Exposed DSL pattern (as opposed to DAO). However, creating many-to-many relationships seem to only be possible using the DAO approach.
I know it is probably fine to use the two patterns interchangeably, but as I set up my project and move forward, I'm wondering what the best approach is from the perspective of code quality. Use them both or switch to DAO? Or the third option is that this question represents a misguided understanding of Kotlin and/or Exposed (new to both), in which case, where am I going wrong? Thanks in advance
It is possible to use DSL to create many-to-many relationships for tables. However whether or not you should use DSL or DAO or both together would really be up to you and whether or not it makes the code easier or harder to read and maintain.
Here is a basic example of a many to many relationship with DSL.
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
object Users : IntIdTable() {
val username = varchar("username", 50)
}
object Permissions : IntIdTable() {
val name = varchar("name", 50)
}
object UserPermissionsJunctionTable : IntIdTable() {
val user = reference("user", Users)
val permission = reference("permission", Permissions)
}
fun main(args: Array<String>) {
val db = Database.connect("jdbc:sqlite:test.db", "org.sqlite.JDBC")
transaction {
addLogger(StdOutSqlLogger)
SchemaUtils.create(Users, Permissions, UserPermissionsJunctionTable)
val userId = Users.insertAndGetId {
it[username] = "john.smith"
}
val readPermissionId = Permissions.insertAndGetId {
it[name] = "read"
}
val writePermissionId = Permissions.insertAndGetId {
it[name] = "write"
}
UserPermissionsJunctionTable.insert {
it[user] = userId
it[permission] = readPermissionId
}
UserPermissionsJunctionTable.insert {
it[user] = userId
it[permission] = writePermissionId
}
val result = Users
.join(UserPermissionsJunctionTable, JoinType.INNER, additionalConstraint = {
Users.id eq UserPermissionsJunctionTable.user
})
.join(Permissions, JoinType.INNER, additionalConstraint = {
UserPermissionsJunctionTable.permission eq Permissions.id
})
.slice(Users.username, Permissions.name).selectAll().map {
it[Users.username] to it[Permissions.name]
}
println(result)
}
}
This prints [(john.smith, read), (john.smith, write)]