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

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中不应该有任何延迟。

我怎么能在通道对象中有最近的Converstaion对象？

Answer 1

您可以有一个LastConversation，它是Chanel内部的Conversation对象。每次更新lastConversation时，您都必须通过修改Room层中的表Chanel来更新它。（更新db不需要那么多性能）。通过实现Chanel列表的排序（可比）。您的UI更新会很酷。并且您来自UI或ViewModel的逻辑更简单。我也是这样做的。

Answer 2

我建议创建另一个类(不是在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

然后像这样在你的刀里使用它:

 @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对象，这就足够了吗？

不应在频道类中包含“对话”，因为“房间”将在“对话”表中为其创建一些列。