I'm currently writing an Android ecommerce app. In the cart, there are increase and decrease buttons for the quantity of an item(cart data is stored locally).
Below are the two queries used to increase and decrease the quantity based on the buttons. The issue is that when the quantity is decreased, it goes to negative values
Thank you
StoreDao.kt
@Query("UPDATE cart_database SET quantity = quantity - 1 where id = :id ")
suspend fun subtractQuantity(id: Int): Int
@Query("UPDATE cart_database SET quantity = quantity 1 where id= :id ")
suspend fun addQuantity(id: Int): Int
CodePudding user response:
If the lower bound is 0 and the upper bound 10 then you could use :-
@Query("UPDATE cart_database SET quantity = max(quantity - 1,0) where id = :id ")
suspend fun subtractQuantity(id: Int): Int
@Query("UPDATE cart_database SET quantity = min(quantity 1,10) where id= :id ")
suspend fun addQuantity(id: Int): Int
CodePudding user response:
Add another condition in the WHERE
clause that prevents the update of the quantity if it is currently 0
:
UPDATE cart_database
SET quantity = quantity - 1
WHERE id = :id AND quantity > 0;
This requirement, could also be solved at the table level, by defining a CHECK
constraint, like this:
CREATE TABLE cart_database (
id INTEGER PRIMARY KEY,
quantity INTEGER CHECK(quantity >= 0)
);
which will not allow negative values in the column quantity.