Room查询构建报错：返回字段存在却提示缺失

问题代码及错误信息 #

DAO方法 #

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

数据类定义 #

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

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
)

构建错误提示 #

查询返回的列在ChatWithParticipantsAndLastMessage中不存在字段[chatId,localMessageId,createdAt,senderId,isEdited,isViewed,text,type,id,chatId,firstName,lastOnline,isOnline,username]，尽管它们被标记为非空或基本类型。

查询返回的列：[id,type,createdAt,ownerId,participantsCount,participantsLimit,unreadCount,lastMessageId,chatId,localMessageId,createdAt,senderId,isEdited,isViewed,text,type,id,chatId,avatarUrl,firstName,lastName,lastOnline,isOnline,username]

原因分析 #

1. 重复列名冲突：查询返回的列存在大量重复名称，比如id（来自ChatEntity和ChatParticipantEntity）、createdAt（来自ChatEntity和ChatMessageEntity）、type（来自ChatEntity和ChatMessageEntity）、chatId（来自ChatMessageEntity和ChatParticipantEntity）。Room无法区分这些重复列，无法正确映射到对应的实体字段。
2. @Embedded前缀未与SQL查询对应：虽然给lastMessage和owner添加了@Embedded(prefix = "...")注解，但SQL查询使用SELECT *返回原始列名，没有给关联表的列添加对应的前缀，导致Room无法将原始列名匹配到带前缀的实体字段上。

解决方案 #

修改SQL查询，显式指定所有需要返回的列，并给ChatMessageEntity和ChatParticipantEntity（owner）的列添加对应前缀，确保列名与@Embedded前缀完全匹配：

@Query("""
    SELECT 
        -- ChatEntity的列，无需前缀
        c.id, c.type, c.createdAt, c.ownerId, c.participantsCount, 
        c.participantsLimit, c.unreadCount, c.lastMessageId,
        -- ChatMessageEntity的列，添加last_message_前缀
        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,
        -- ChatParticipantEntity（owner）的列，添加owner_前缀
        p.id AS owner_id,
        p.chatId AS owner_chatId,
        p.avatarUrl AS owner_avatarUrl,
        p.firstName AS owner_firstName,
        p.lastName AS owner_lastName,
        p.lastOnline AS owner_lastOnline,
        p.isOnline AS owner_isOnline,
        p.username AS owner_username
    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>>

注意：@Relation注解的participants字段由Room自动查询关联数据，不需要在主查询中返回相关列，只需确保主查询正确返回ChatEntity的主键即可。

内容的提问来源于stack exchange，提问作者Calamity