DAO Query to Swap Boolean Values in ROOM Database

Question

I am working on a picture app where I have a list of pictures entities cached in a ROOM database:

This is my Picture Entity class:

@Entity
data class PictureEntity(

    @PrimaryKey
    val id: String,
    val title: String,
    val url: String,
    var isFavorite:Boolean)

I am looking to toggle the boolean value of isFavorite field when a user hits the Mark/Unmark favorite button in the UI for that particular picture item's isFavorite field

I am using this dao query but it is updating all the fields for isFavorite column.

@Update(onConflict = OnConflictStrategy.REPLACE)
suspend fun updatePicture(picture: PictureEntity)

What I want is to just update one single field for the picture which has been selected favorite.

I tried this query too but I got an error:

@Query("UPDATE picture_table SET isFavorite = NOT isFavorite")
suspend fun updatePicture()

I am totally lost and can't find any way around this issue. I will appreciate any help to get me back on track.

Answer 1

I am using this dao query but it is updating all the fields for isFavorite column.

That's how the convenience @Update works, it finds the appropriate row according to the primary key and updates all of the other columns.

I tried this query too but I got an error: @Query("UPDATE picture_table SET isFavorite = NOT isFavorite")

You have the wrong table name, it is, according to the provided code, pictureEntity. Furthermore, you have no clause limiting the update to a specific row, therefore it would toggle ALL rows in the table.

You should be using something like:-

@Query("UPDATE pictureEntity SET isFavorite = NOT isFavorite WHERE id=:id")

As an example consider the following snippet :-

    ....

    dao.insert(PictureEntity("Picture1","Picture1","TheURL",false))
    dao.insert(PictureEntity("Picture2","Picture2","TheURL",true))
    dao.insert(PictureEntity("Picture3","Picture3","TheURL",false))

    logPictures("STAGE1")
    dao.toggleFavorite("Picture2")
    logPictures("STAGE2")
}

fun logPictures(tagSuffix: String) {
    for(pe in dao.getAllPictures()) {
        Log.d("DBINFO_$tagSuffix","Picture is ${pe.id} Title is ${pe.title} isFavorite is ${pe.isFavorite}")
    }
}

The result being :-

D/DBINFO_STAGE1: Picture is Picture1 Title is Picture1 isFavorite is false
D/DBINFO_STAGE1: Picture is Picture2 Title is Picture2 isFavorite is true
D/DBINFO_STAGE1: Picture is Picture3 Title is Picture3 isFavorite is false


D/DBINFO_STAGE2: Picture is Picture1 Title is Picture1 isFavorite is false
D/DBINFO_STAGE2: Picture is Picture2 Title is Picture2 isFavorite is false
D/DBINFO_STAGE2: Picture is Picture3 Title is Picture3 isFavorite is false

• i.e. Picture2 has been toggled from true to false, Picture1 and Pictuire3 remain as they were (false)