如何在Android Room中加入同一对象的多个实例? [英] How to join multiple instances of same object in Android Room?

问题描述

注意:我无法使用关系，因为我们遇到了直接连接查询中未再现的性能问题.

NOTE: I cannot use relation as we had performance issue which is not reproduced on direct join query.

直到我添加target user和group user以及相应的

Until I added target user and from group user and corresponding

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

效果很好.但是添加之后，我不知道如何在查询中使这些前缀映射.

It worked well. But after addition, I can not figure out how to make those prefixes map in the query.

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:错误:查询存在问题:[SQLITE_ERROR] SQL错误或数据库丢失(列名模糊:main.chat_user.chat_user_id)

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)

推荐答案

如Moayad .AlMoghrabi所述，我应该使用表别名.因此，我将LEFT JOIN chat_user重命名为LEFT JOIN chat_user target_user.请注意，chat-user和target_user之间没有AS. (我的错误是我试图将其放在那里).前缀的行为对我来说仍然是一个谜，因为chat_user的前缀可以正常工作，但是如果将前缀放在ChatUserRelationEntity上，则会收到未知的列错误.

as mentioned Moayad .AlMoghrabi I should use table aliases. So I renamed LEFT JOIN chat_user to LEFT JOIN chat_user target_user. Note there is no AS between chat-user and target_user. (my mistake was that I tried to put it there). Behavior of prefixes is still a mystery to me as the prefix for chat_user works properly, but if I put the prefix to ChatUserRelationEntity then I receive unknown column error.

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_relation_chat_user_id = message_item.message_user_id
    AND chat_user_relation.chat_user_relation_chat_user_chat_id = :chatId
LEFT JOIN chat_user target_user 
    ON target_user.chat_user_id = message_item.messages_target_user
LEFT JOIN chat_user from_group_user 
    ON from_group_user.chat_user_id = message_item.messages_from_group_user
WHERE message_item.message_chat_id = :chatId
ORDER BY message_created_at ASC

这篇关于如何在Android Room中加入同一对象的多个实例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！