The columns returned by the query do not have the fields even though they are there

Question

Having the following DAO method:

@Query("""
    SELECT * FROM ChatEntity c
    JOIN ChatMessageEntity m ON c.id = m.chatId
    JOIN ChatParticipantEntity p ON p.id = c.ownerId
    WHERE c.id IN (
        SELECT id FROM ChatEntity
        LIMIT :limit OFFSET :offset
    )
    ORDER BY m.createdAt DESC
    
""")
fun getChats(
    limit: Int,
    offset: Int
): Flow<List<ChatWithParticipantsAndLastMessage>>

Where ChatWithParticipantsAndLastMessage is:

data class ChatWithParticipantsAndLastMessage(
    @Embedded
    val chatEntity: ChatEntity,

    @Embedded(prefix = "last_message_")
    val lastMessage: ChatMessageEntity,

    @Embedded(prefix = "owner_")
    val owner: ChatParticipantEntity,

    @Relation(
        entity = ChatParticipantEntity::class,
        parentColumn = "id",
        entityColumn = "chatId"
    )
    val participants: List<ChatParticipantEntity>
)

And corresponding @Embedded objects are:

data class ChatEntity(
    @PrimaryKey val id: Int,
    val type: String,
    val createdAt: Int,
    val ownerId: Int,
    val participantsCount: Int,
    val participantsLimit: Int,
    val unreadCount: Int,
    val lastMessageId: Int?
)

data class ChatMessageEntity(
    val chatId: Int,
    val localMessageId: Int,
    val createdAt: Int,
    val senderId: Int,
    val isEdited: Boolean,
    val isViewed: Boolean,
    val text: String,
    val type: String
)

data class ChatParticipantEntity(
    @PrimaryKey val id: Int,
    val chatId: Int,
    val avatarUrl: String?,
    val firstName: String,
    val lastName: String?,
    val lastOnline: Int,
    val isOnline: Boolean,
    val username: String
)

All of them are, of course, annotated with @Entity. But build fails with error:

The columns returned by the query does not have the fields [chatId,localMessageId,createdAt,senderId,isEdited,isViewed,text,type,id,chatId,firstName,lastOnline,isOnline,username] in ChatWithParticipantsAndLastMessage even though they are annotated as non-null or primitive.

Columns returned by the query: [id,type,createdAt,ownerId,participantsCount,participantsLimit,unreadCount,lastMessageId,chatId,localMessageId,createdAt,senderId,isEdited,isViewed,text,type,id,chatId,avatarUrl,firstName,lastName,lastOnline,isOnline,username]

But all required fields that Room considers missing are literally there. What gives?

Answer 1

Duplicate column names turned out to be the problem. Room simply doesn't know, for example, which chatId it is seeing: from ChatMessageEntity or form ChatParticipantEntity. Without touching column names themselves, the solution is to use aliases in my query:

// omfg
@Query(
    """
    SELECT c.id, c.type, c.createdAt, c.ownerId, c.participantsCount, c.participantsLimit, c.unreadCount,
    m.chatId AS last_message_chatId, m.localMessageId AS last_message_localMessageId, m.createdAt AS last_message_createdAt, 
        m.senderId AS last_message_senderId, m.isEdited AS last_message_isEdited, m.isViewed AS last_message_isViewed,
        m.text AS last_message_text, m.type AS last_message_type,
    o.id AS owner_id, o.chatId AS owner_chatId, o.avatarUrl AS owner_avatarUrl, o.firstName AS owner_firstName,
        o.lastName AS owner_lastName, o.lastOnline AS owner_lastOnline
    FROM ChatEntity c
    JOIN ChatMessageEntity m ON c.id = m.chatId
    JOIN ChatParticipantEntity o ON o.id = c.ownerId
    WHERE c.id IN (
        SELECT id FROM ChatEntity
        LIMIT :limit OFFSET :offset
    )
    ORDER BY m.createdAt DESC
    
"""
)
fun getChats(
    limit: Int,
    offset: Int
): Flow<List<ChatWithParticipantsAndLastMessage>>

This is sooo ugly and error-prone, but we take what we get, I guess.