Android development Room SQL query

Issue

I want to add an item into a table,but if the item already exists in the table,i wanna modify a column,this is my Dao

@Dao
interface BasketDao {
    @Insert(onConflict  OnConflictStrategy.REPLACE)
    suspend fun upsert(item: BasketItem)

    @Delete
    suspend fun delete(item: BasketItem)

    @Query("SELECT * FROM basket_items")
    fun loadBasketItems(): LiveData<List<BasketItem>>
}

I already have a conflict strategy for the upsert function,to replace the item if it exists,but i want to only modify the value of a column if the item exists,not replace the entire item,what can i do

Solution

You can do this using two database calls (one to query existing item and second to insert/replace the item).

In Dao:

@Query("SELECT * FROM basket_items WHERE name  :name")
suspend fun getItemByName(name: String): BasketItem?

Then in you data layer:

val newItemToSave  //
val existingItem  basketDao.getItemByName(newItemToSave.name)
if(existingItem  null)
    basketDao.upsert(newItemToSave)
else
    basketDao.upsert(existingItem.copy(quantity  existingItem.quantity + 1)

Answered By – Arpit Shukla

Leave a Comment