为聊天模型中的Room实体定义一对多关系

Question

我开始使用Room数据库，并查看了多个文档以创建它的实体。这是我的关系：聊天频道可以有多个对话，所以这是一对多的关系。因此，我创建了以下实体。

频道实体

@Entity(primaryKeys = ["channelId"])
@TypeConverters(TypeConverters::class)
data class Channel(
    @field:SerializedName("channelId")
    val channelId: String,
    @field:SerializedName("channelName")
    val channelName: String,
    @field:SerializedName("createdBy")
    val creationTs: String,
    @field:SerializedName("creationTs")
    val createdBy: String,
    @field:SerializedName("members")
    val members: List<String>,
    @field:SerializedName("favMembers")
    val favMembers: List<String>
) {
  // Does not show up in the response but set in post processing.
  var isOneToOneChat: Boolean = false
  var isChatBot: Boolean = false
}

对话实体

@Entity(primaryKeys = ["msgId"],
    foreignKeys = [
        ForeignKey(entity = Channel::class,
                parentColumns = arrayOf("channelId"),
                childColumns = arrayOf("msgId"),
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
    ])
@TypeConverters(TypeConverters::class)
data class Conversation(

    @field:SerializedName("msgId")
    val msgId: String,
    @field:SerializedName("employeeID")
    val employeeID: String,
    @field:SerializedName("channelId")
    val channelId: String,
    @field:SerializedName("channelName")
    val channelName: String,
    @field:SerializedName("sender")
    val sender: String,
    @field:SerializedName("sentAt")
    val sentAt: String,
    @field:SerializedName("senderName")
    val senderName: String,
    @field:SerializedName("status")
    val status: String,
    @field:SerializedName("msgType")
    val msgType: String,
    @field:SerializedName("type")
    val panicType: String?,
    @field:SerializedName("message")
    val message: List<Message>,
    @field:SerializedName("deliveredTo")
    val delivered: List<Delivered>?,
    @field:SerializedName("readBy")
    val read: List<Read>?

) {

data class Message(
        @field:SerializedName("txt")
        val txt: String,
        @field:SerializedName("lang")
        val lang: String,
        @field:SerializedName("trans")
        val trans: String
)

data class Delivered(
        @field:SerializedName("employeeID")
        val employeeID: String,
        @field:SerializedName("date")
        val date: String
)

data class Read(
        @field:SerializedName("employeeID")
        val employeeID: String,
        @field:SerializedName("date")
        val date: String
)

    // Does not show up in the response but set in post processing.
    var isHeaderView: Boolean = false
}

现在，您可以看到对话属于频道。当用户查看频道列表时，我需要在列表项中显示最后一次对话的多个属性。我的问题是，如果我只声明以上关系是否足够，还是应该在频道类中包含会话对象？我还能以哪些其他方式处理它？因为当用户滚动时，UI 需要获取发生的最近一次对话的时间、状态等信息，并在频道列表的每个项目中显示，所以查询时不应出现任何 UI 延迟。

如何在频道对象中拥有最新的对话对象？

- androidbash

Answer 1

我建议创建另一个类（不在数据库中，只用于UI显示），如下所示：

data class LastConversationInChannel(
    val channelId: String,
    val channelName: String,
    val creationTs: String,
    val createdBy: String,
    val msgId: String,
    val employeeID: String,
    val sender: String,
    val sentAt: String,
    val senderName: String
    .
    .
    .
)

通过此查询获取每个频道中的最后一次对话：

 SELECT Channel.*
 ,IFNULL(LastConversation.msgId,'') msgId
 ,IFNULL(LastConversation.sender,'') sender
 ,IFNULL(LastConversation.employeeID,'') employeeID
 ,IFNULL(LastConversation.sentAt,'') sentAt
 ,IFNULL(LastConversation.senderName,'') senderName
 from Channel left join 
 (SELECT * from Conversation a  
 WHERE a.msgId IN ( SELECT b.msgId  FROM Conversation AS b 
                    WHERE a.channelId = b.channelId 
                    ORDER BY b.sentAt DESC  LIMIT 1 )) as LastConversation
 on Channel.channelId = LastConversation.channelId

然后在您的dao中像这样使用它：

 @Query(" SELECT Channel.*\n" +
            " ,IFNULL(LastConversation.msgId,'') msgId\n" +
            " ,IFNULL(LastConversation.sender,'') sender\n" +
            " ,IFNULL(LastConversation.employeeID,'') employeeID\n" +
            " ,IFNULL(LastConversation.sentAt,'') sentAt\n" +
            " ,IFNULL(LastConversation.senderName,'') senderName\n" +
            " from Channel left join \n" +
            " (SELECT * from Conversation a  \n" +
            " WHERE a.msgId IN ( SELECT b.msgId  FROM Conversation AS b \n" +
            "                    WHERE a.channelId = b.channelId \n" +
            "                    ORDER BY b.sentAt DESC  LIMIT 1 )) as LastConversation\n" +
            " on Channel.channelId = LastConversation.channelId")
    fun getLastConversationInChannel(): LiveData<List<LastConversationInChannel>>

“我只需要像上面那样声明关系，就足够了吗？还是应该将Conversation对象包含在Channel类中？”

“你不应该将Conversation包含在Channel类中，因为Room会在Conversation表中为它创建一些列。”

- Iman Hamidi

Answer 2

你可以在Chanel内拥有一个LastConversation，它是一个Conversation对象。每次通过修改来自Room层的Chanel表更新lastConversation时，您必须更新此内容。(不会对更新数据库造成太大的性能影响)。通过为Chanel列表实现排序(可比较)，您的UI更新将更加出色。并且您从UI或ViewModel中提取的逻辑也将更加简单。我也是这样做的。

- Thành Hà Văn