Update Query in Child Table won't relect in parent table Android Room Kotlin

Question

Parent Table :

@Entity(tableName = "Product")
data class Products (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int = 0,

    @ColumnInfo(name = "name")
    var name  : String? = null,

    @ColumnInfo(name = "variants")
    var variants : MutableList<Variants> = mutableListOf()
)

Child Table :

@Entity(tableName = "Variant")
data class Variants (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int  = 0,

    @ColumnInfo(name = "product_id", index = true)
    var product_id : Int?  = null,

    @ColumnInfo(name = "measurement")
    var measurement : String?  = null,

    @ColumnInfo(name = "discounted_price")
    var discounted_price : String?  = null,

    @ColumnInfo(name = "cart_count")
    var cart_count : Int?  = null
)

i want to update the cart_count in variant and it should also reflect in product table also as variant updated .. what is the query for this ??

When i use this Update query ..i update the value in Variant Table but when i get getallProducts , the variant table shows old value instead of new updated value

My Update Query :

@Query("UPDATE Variant SET cart_count= :cart_count, is_notify_me= :is_Notify,product_id= :product_id WHERE id = :id")
    fun updateVariant(id: Int,is_Notify:Boolean, cart_count: String,product_id: Int) : Int

It doesn't work when i getProducts using this Query :

@Transaction
@Query("SELECT * FROM Product WHERE subcategory_id=:subcatid")
fun getAllProducts(subcatid:Int): Flow<MutableList<Products>>

Actually the Get Query is Correct but Update query is wrong

Answer 1

Create data class like below

data class ProductWithVariants(
  @Embedded val product: Product,
  @Relation(
    parentColumn = "id",
    entityColumn = "productId"
  )
  var variamts: List<Variant>? = null,
)

And in the Dao

@Transaction
@Query("SELECT * FROM product WHERE id:id")
suspend fun getProduct(id: Int): List<ProductWithVariants>

That's all, After the update variant select query will fetch the data from both tables and combine it.

You can add a foreign key for referral integrity.