android - 如何在 Android Room 中加入同一对象的多个实例？

Question

注意:我不能使用关系，因为我们遇到了直接连接查询中没有重现的性能问题。

直到我添加 target user来自 group user和相应的

LEFT JOIN chat_user ON chat_user.chat_user_id = message_item.messages_target_user
LEFT JOIN chat_user ON chat_user.chat_user_id = message_item.messages_from_group_user

它运作良好。但是在添加之后，我无法弄清楚如何在查询中映射这些前缀。

class ReadMessageEntity(
  @Embedded
  var message: MessageEntity,
  @Embedded
  var block: BlockEntity?,
  @Embedded
  var user: ChatUserRelationEntity,
  @Embedded(prefix = "target_user_")
  var targetUser: ChatUserEntity?,
  @Embedded(prefix = "from_group_user_")
  var fromGroupUser: ChatUserEntity?
)

这是我要查询的:

  @Transaction
  @Query("""
    SELECT * FROM message_item 
    LEFT JOIN block_item ON block_item.block_message_id = message_item.message_local_id
    LEFT JOIN chat_user_relation ON chat_user_relation.chat_user_id = message_item.message_user_id
    LEFT JOIN chat_user ON chat_user.chat_user_id = message_item.messages_target_user
    LEFT JOIN chat_user ON chat_user.chat_user_id = message_item.messages_from_group_user
    WHERE message_item.message_chat_id = :chatId
    ORDER BY message_created_at ASC
    """)
  fun getMessagesByChat(chatId: String): Single<List<ReadMessageEntity>>

错误:

e: error: There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (ambiguous column name: main.chat_user.chat_user_id)

Answer 1

在查询中，您必须为表 设置别名。聊天用户 因为您以相同的名称加入它两次，这会使数据库引擎感到困惑，因此它会告诉您该字段不明确。
编辑:
我发现了可能与您的问题类似的内容:https://github.com/JetBrains/Exposed/issues/177
他们还将此代码作为解决此问题的示例:

    object Users : Table() {
        val id = varchar("id", 10).primaryKey()
        val name = varchar("name", length = 50)
        val residentialCityId = optReference("resid_city_id", Cities)
        val bornCityId = optReference("born_city_id", Cities)
    }

    object Cities : IntIdTable() {
        val name = varchar("name", 50) // Column
    }
    fun test() {
        val userTable1 = Users.alias("u1")
        val userTable2 = Users.alias("u2")

        Cities
            .innerJoin(userTable1, {Cities.id}, {userTable1[Users.residentialCityId]})
            .innerJoin(userTable2, {Cities.id}, {userTable2[Users.bornCityId]})
            .selectAll()
    }