如何在Kotlin Exposed多对多关联中添加额外字段并创建实体

在Exposed框架中，当多对多关联需要额外字段时，不能用默认的隐式关联，必须将中间表升级为带实体的显式关联，以下是具体实现步骤：

1. 修改中间表Members，添加额外字段并设置复合主键 #

首先扩展Members表，加入所需额外字段（比如成员角色、加入时间），同时将user和server设为复合主键，确保关联唯一性：

// 示例：定义成员角色枚举
enum class MemberRole {
    ADMIN, MODERATOR, MEMBER
}

object Members : UUIDTable("members") {
    val user = reference("user", Users, onDelete = ReferenceOption.CASCADE)
    val server = reference("server", Servers, onDelete = ReferenceOption.CASCADE)
    // 添加额外业务字段
    val joinAt = datetime("join_at").default(DateTime.now())
    val role = enumeration<MemberRole>("role").default(MemberRole.MEMBER)
    
    // 设置复合主键，避免重复关联
    override val primaryKey = PrimaryKey(user, server)
}

2. 创建Member实体类 #

为中间表创建对应的实体类，封装关联关系和额外字段，简化数据操作：

class Member(id: EntityID<UUID>) : UUIDEntity(id, Members) {
    companion object : UUIDEntityClass<Member>(Members)

    var user by User referencedOn Members.user
    var server by Server referencedOn Members.server
    var joinAt by Members.joinAt
    var role by Members.role
}

3. 更新Server和User类的关联逻辑 #

替换原有的隐式关联，改为通过Member实体建立显式关联：

修改Server类 #

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
    // 替换原关联，通过Member实体获取服务器成员列表（含额外字段）
    val members by Member referrersOn Members.server
}

修改User类 #

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
    // 添加用户的服务器成员身份关联
    val serverMemberships by Member referrersOn Members.user
}

4. 常用操作示例 #

给服务器添加成员（含额外字段） #

transaction {
    val targetUser = User.findById(userUUID) ?: return@transaction
    val targetServer = Server.findById(serverUUID) ?: return@transaction
    
    // 先检查是否已存在关联，避免重复添加
    if (Member.find { (Members.user eq targetUser.id) and (Members.server eq targetServer.id) }.empty()) {
        Member.new {
            user = targetUser
            server = targetServer
            role = MemberRole.MODERATOR // 自定义角色
            // joinAt使用默认值，也可手动指定：joinAt = DateTime.now()
        }
    }
}

查询服务器成员及额外信息 #

transaction {
    val server = Server.findById(serverUUID) ?: return@transaction
    server.members.forEach { member ->
        println("用户名: ${member.user.username}, 角色: ${member.role}, 加入时间: ${member.joinAt}")
    }
}

查询用户的所有服务器身份 #

transaction {
    val user = User.findById(userUUID) ?: return@transaction
    user.serverMemberships.forEach { membership ->
        println("服务器名: ${membership.server.name}, 角色: ${membership.role}")
    }
}

内容的提问来源于stack exchange，提问作者zxcqirara