Wednesday, 31 July 2019

"no such column" SQL error from Android Room and multiple @Embedded fields on POJO

I have a nested POJO structure that should represent a single Game having multiple Rounds and each Round has a single Topic associated with it:

class GameWithRounds {
    @Embedded
    var game: Game? = null

    @Relation(
        parentColumn = "id",
        entityColumn = "gameId",
        entity = RoundRoom::class
    )
    var rounds: List<RoundWithTopic>? = null
}

class RoundWithTopic(
    @Embedded
    var round: RoundRoom,

    @Embedded(prefix = "topic_")
    var topic: Topic
)

The embedded annotation on Topic specifies a prefix because there are clashing id properties.

The Room Query that can fetch those classes is:

@Query("SELECT Topic.id as topic_id, Topic.name as topic_name, (...), RoundRoom.* FROM RoundRoom INNER JOIN Topic ON RoundRoom.topicId = Topic.id")
    abstract fun findRoundsWithTopics(): List<RoundWithTopic>

However, building the project gives me Room errors:

There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such column: topic_id)

Even though when I induce a warning about which fields are actually present, this is what Room tells me:

Columns returned by the query: topic_id, topic_name, topic_description, topic_language, topic_keywords, topic_sourceUrls, topic_furtherUrls, topic_questions, order, gameId, topicId, status, id. Fields in cz.melkamar.sklapecka.model.RoundWithTopic: order, gameId, topicId, status, id, topic_id, topic_name, topic_description, topic_language, topic_keywords, topic_sourceUrls, topic_furtherUrls, topic_questions, topic_image.

The topic_id column is there in the query result! Why am I getting this error?


For completeness, this is the entities:

@Entity
data class Game(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,

    @Embedded
    val gameConfigurationEmbed: GameConfigurationEmbed
)

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Game::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("gameId"),
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Topic::class,
            parentColumns = arrayOf("id"),
            childColumns = arrayOf("topicId"),
            onDelete = ForeignKey.CASCADE
        )
    ]
)
@TypeConverters(RoomConverters::class)
data class RoundRoom(
    val order: Int,
    var gameId: Long,
    val topicId: String,
    var status: RoundStatus = RoundStatus.CREATED,

    @PrimaryKey(autoGenerate = true)
    val id: Long = 0
) {
    enum class RoundStatus {
        CREATED, UPCOMING, IN_PROGRESS, FINISHED
    }
}

@Entity
data class Topic(
    @PrimaryKey val id: String,
    val name: String,
    val description: String,
    val language: String,
    val keywords: List<String>,
    val sourceUrls: List<String>,
    val furtherUrls: List<String>,
    val questions: List<String>,
    val image: ByteArray?
)



from "no such column" SQL error from Android Room and multiple @Embedded fields on POJO

No comments:

Post a Comment