kotlin - How to join multiple instances of same object in Android Room?

Question

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

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?
)

This is which I'm trying to query:

  @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>>

The error:

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

In the query, you have to set an alias for the table chat_user as you are joining it twice in the same name which will confuse database engine so it tells you that the field is ambiguous.

EDIT:

I found something that may be similar to your issue: https://github.com/JetBrains/Exposed/issues/177

Also they referred to this code as an example to fix this issue:

    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()
    }