查询返回的列没有字段,尽管它们存在。

huangapple go评论84阅读模式
英文:

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

问题

以下是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>>

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

对应的@Embedded对象是:

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
)

它们当然都带有@Entity注解。但是构建失败并显示以下错误信息:

查询返回的列在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]

但是,Room认为缺少的所有必需字段实际上都存在。出现了什么问题?

英文:

Having the following DAO method:

@Query(&quot;&quot;&quot;
    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
    
&quot;&quot;&quot;)
fun getChats(
    limit: Int,
    offset: Int
): Flow&lt;List&lt;ChatWithParticipantsAndLastMessage&gt;&gt;

Where ChatWithParticipantsAndLastMessage is:

data class ChatWithParticipantsAndLastMessage(
    @Embedded
    val chatEntity: ChatEntity,

    @Embedded(prefix = &quot;last_message_&quot;)
    val lastMessage: ChatMessageEntity,

    @Embedded(prefix = &quot;owner_&quot;)
    val owner: ChatParticipantEntity,

    @Relation(
        entity = ChatParticipantEntity::class,
        parentColumn = &quot;id&quot;,
        entityColumn = &quot;chatId&quot;
    )
    val participants: List&lt;ChatParticipantEntity&gt;
)

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?

答案1

得分: 0

让我们给Room提供一个查询管理器,使查询变得简单,就像"SELECT * FROM ChatEntity"一样。

我无法重现这个案例,但是下面的代码可以编译通过。

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

@Entity(primaryKeys = ["chatId", "localMessageId", "senderId"])
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
)

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

data class ChatWithParticipantsAndLastMessage(
    @Embedded
    val chatEntity: ChatEntity,

    @Relation(
        entity = ChatMessageEntity::class,
        parentColumn = "id",
        entityColumn = "chatId"
    )
    val lastMessage: ChatMessageEntity,

    @Relation(
        entity = ChatParticipantEntity::class,
        parentColumn = "ownerId",
        entityColumn = "id"
    )
    val owner: ChatParticipantEntity,

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

@androidx.room.Dao
interface ChatDao {
    @Transaction
    @Query("SELECT * FROM ChatEntity")
    fun getChats(): Flow<List<ChatWithParticipantsAndLastMessage>>
}

以上是翻译好的内容。

英文:

Let give a QUERY management to Room - make a Query as simple as SELECT * FROM ChatEntity =>
I can't reproduce the case - but this one below compiles.

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

@Entity(primaryKeys = [&quot;chatId&quot;, &quot;localMessageId&quot;, &quot;senderId&quot;]) /*can&#39;t catch your PrimaryKeys*/
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
)

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

data class ChatWithParticipantsAndLastMessage(
    @Embedded
    val chatEntity: ChatEntity,

    @Relation(
        entity = ChatMessageEntity::class,
        parentColumn = &quot;id&quot;,
        entityColumn = &quot;chatId&quot;
    )
    val lastMessage: ChatMessageEntity,

    @Relation(
        entity = ChatParticipantEntity::class,
        parentColumn = &quot;ownerId&quot;,
        entityColumn = &quot;id&quot;
    )
    val owner: ChatParticipantEntity,

    @Relation(
        entity = ChatParticipantEntity::class,
        parentColumn = &quot;id&quot;,
        entityColumn = &quot;chatId&quot;
    )
    val participants: List&lt;ChatParticipantEntity&gt;
)

@androidx.room.Dao
interface ChatDao {
    @Transaction
    @Query(&quot;SELECT * FROM ChatEntity&quot;)
    fun getChats(
    ): Flow&lt;List&lt;ChatWithParticipantsAndLastMessage&gt;&gt;
}

答案2

得分: 0

重复的列名是问题的原因。例如,Room无法确定它所看到的chatId是来自ChatMessageEntity还是ChatParticipantEntity。在不更改列名的情况下,解决方案是在查询中使用别名:

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

这样做很丑陋且容易出错,但我想我们只能接受现实。

英文:

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(
    &quot;&quot;&quot;
    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
    
&quot;&quot;&quot;
)
fun getChats(
    limit: Int,
    offset: Int
): Flow&lt;List&lt;ChatWithParticipantsAndLastMessage&gt;&gt;

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

huangapple
  • 本文由 发表于 2023年8月8日 23:42:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/76861160.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定