Additional fields in many-to-many references

Question

I need to create many-to-many reference that will contain additional fields. Eg I have

object Servers : UUIDTable("servers") {
    val name = text("name")
    val description = text("description").nullable()
    val link = text("link").nullable()
    val privacy = enumeration<ServerPrivacy>("privacy").default(ServerPrivacy.PUBLIC)
    val owner = reference("owner", Users, onDelete = ReferenceOption.CASCADE)
}

class Server(id: EntityID<UUID>) : UUIDEntity(id, Servers) {
    companion object : UUIDEntityClass<Server>(Servers)

    var name by Servers.name
    var description by Servers.description
    var link by Servers.link
    var privacy by Servers.privacy
    var owner by User referencedOn Servers.owner
    val categories by Category referrersOn Categories.server
    val members by Members.server
}

object Users : UUIDTable("users") {
    val login = text("login")
    val password = text("password")
    val username = text("username")
    val link = text("link").nullable()
    val locale = enumeration<LanguageRegion>("locale")
    val flags = integer("flags").default(0)
    val isBanned = bool("is_banned").default(false)
}
class User(id: EntityID<UUID>) : UUIDEntity(id, Users) {
    companion object : UUIDEntityClass<User>(Users)

    var login by Users.login
    var password by Users.password
    var username by Users.username
    var link by Users.link
    var locale by Users.locale
    var flags by Users.flags
    var isBanned by Users.isBanned
}

and I need to link Users on Servers with many-to many reference. I created table like

object Members : UUIDTable("members") {
    val user = reference("user", Users, onDelete = ReferenceOption.CASCADE)
    val server = reference("server", Servers, onDelete = ReferenceOption.CASCADE)
}

and understood that I need to contain another information in the Members and also will be helpful if I'll create entity like Member for easier data manipulation

Alexey Soshin · Answer 1 · 2022-11-19T12:05:20.373

I know I already answered you on GitHub, but will post it here as well for those that are looking to represent many-to-many relationship with Exposed DAO.

What you're missing is the use of via function. It's documented, but maybe we could do a better job.


object Servers : UUIDTable("servers") {
    val name = text("name").default("dummy server")
    val description = text("description").nullable()
    val link = text("link").nullable()
    val privacy = enumeration<ServerPrivacy>("privacy").default(ServerPrivacy.PUBLIC)
    val owner = reference("owner", Users, onDelete = ReferenceOption.CASCADE)
}

enum class ServerPrivacy {
    PUBLIC
}

class Server(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<Server>(Servers)

    var name by Servers.name
    var description by Servers.description
    var link by Servers.link
    var privacy by Servers.privacy
    var owner by User referencedOn Servers.owner
    val members by User.via(Members.server, Members.user)
}

object Users : UUIDTable("users") {
    val username = text("username").default("dummy")
    val link = text("link").nullable()
    val flags = integer("flags").default(0)
    val isBanned = bool("is_banned").default(false)
}
class User(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<User>(Users)

    var username by Users.username
    var link by Users.link
    var flags by Users.flags
    var isBanned by Users.isBanned
    val servers by Server.via(Members.user, Members.server)
}

object Members : UUIDTable("members") {
    val user = reference("user", Users, onDelete = ReferenceOption.CASCADE)
    val server = reference("server", Servers, onDelete = ReferenceOption.CASCADE)
}

Additional fields in many-to-many references

1 Answers1