时间戳

首页 > 解决方案 > 为聊天模型定义房间实体之间的一对多关系

android - 为聊天模型定义房间实体之间的一对多关系

问题描述

我开始使用 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
}

现在你可以看到Conversation属于一个Channel。当用户看到频道列表时，我需要在列表项中显示最后一次对话的几个属性。我的问题是，如果我只是像上面那样声明关系就足够了，还是应该在 Channel 类中包含 Converstion 对象？我可以通过哪些其他方式来处理它？因为用户滚动时，UI 需要在频道列表的每个项目中获取最近发生的对话以及时间、状态等。因此，当我查询时，UI 不应该有任何滞后。

我怎样才能在 Channel 对象中拥有最近的 Converstaion 对象？

标签： androiddatabaseandroid-roomone-to-manyandroid-database

解决方案

我建议创建另一个类（不在 DB 中，仅用于在 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>>

如果我只是像上面那样声明关系就足够了，还是应该在 Channel 类中包含 Converstion 对象？

您不应该在 Channel 类中包含 Conversation，因为 Room 会在 Conversation 表中为其创建一些列。

推荐阅读