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.

Answer 2

I believe that you misunderstand the relationships.

That is, you have a list (MutableList) stored in the Product table, but are updating the row (if one) of the Variant in the variant table, you are then appearing to extract the Product and thus the reconstructed Variant stored in the Product table, not the updated Variant that is a child (again if it exists) in the Variant table.

There is probably no reason to have the Products include :-

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

And my guess is this is what is throwing you.

Example/Demo

Perhaps consider this demonstration of what may be confusing you. The demo is based upon your code although there are some alterations (typically commented). The demo is purposefully wrong as it includes both data being store as part of the product and also the exact same core data being store in the Variant table (the latter being the better for your situation of say updating the cart_count).

The Products data class:-

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

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int? = null, /* allows generation of the id, if need be (should really be Long though )*/

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

    @ColumnInfo(name = "variants")
    //var variants : MutableList<Variants> = mutableListOf()
    /* Note changed to suit com.google.code.Gson */
    /* Note probably not a required column anyway */
    var variantsMutableListHolder: VariantsMutableListHolder
)

• The main change is just to suit what little I know of JSON. However as the suggestion is that variantsMutableListHolder is not required, ignore this.

VariantsMutableListHolder (not required for the suggested solution):-

class VariantsMutableListHolder(
    val variantsMutableList: MutableList<Variants>
)

Converters (for the VariantsMutableListHolder, again not required for the suggested solution) :-

class Converters {
    @TypeConverter
    fun fromVariantsMutableListToJSONString(variantsMutableListHolder: VariantsMutableListHolder): String = Gson().toJson(variantsMutableListHolder)
    @TypeConverter
    fun fromJSONStringToVariantsMutableListHolder(jsonString: String): VariantsMutableListHolder=Gson().fromJson(jsonString,VariantsMutableListHolder::class.java)
}

Variants (mainly suggested changes for referential integrity (protect against orphans)) :-

@Entity(
    tableName = "Variant",
    /* You may wish to consider adding Foreign Key constraints */
    /*  FK constraints enforce referential integrity*/
    foreignKeys = [
        ForeignKey(
            entity = Products::class, /* The Parent Class */
            parentColumns = ["id"], /* The column or columns (if composite key) that map to the parent */
            childColumns = ["product_id"], /* the column or columns in the child that reference the parent */
            /* Optional but assists in maintaining referential integrity automatically */
            onDelete = ForeignKey.CASCADE, /* if a parent is deleted then so are the children */
            onUpdate = ForeignKey.CASCADE /* if the reference column in the parent is changed then the value in the children is changed */
        )
    ]
)
data class Variants (

    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    var id : Int?  = null, /* allows generation of id, if null passed */

    @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
)

ProductsWithRelatedVariants NEW IMPORTANT class:-

/* Class for retrieving the Product with the children from the Variant table */
data class ProductsWithRelatedVariants(
    @Embedded
    var products: Products,
    @Relation(
        entity = Variants::class, /* The class of the Children */
        parentColumn = "id", /* the column in the parent table that is referenced */
        entityColumn = "product_id" /* The column in the child that references the parent*/
    )
    var variantsList: List<Variants>
)

AllDao all the dao functions (note no Flows/Suspends as mainthread used for the demo) :-

@Dao
interface AllDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(products: Products): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(variants: Variants): Long
    /* adjusted to suit code in question */
    @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
    @Query("SELECT * FROM Variant WHERE id=:id")
    fun getVariantsById(id: Int): Variants
    /* adjusted to suit code in question */
    @Transaction
    @Query("SELECT * FROM Product /*WHERE subcategory_id=:subcatid*/")
    fun getAllProducts(/*subcatid:Int*/): /*Flow<*/MutableList<Products>/*>*/ /*As run on main thread no flow needed */
    @Transaction
    @Query("SELECT * FROM Product")
    fun getAllProductsWithTheRelatedVariants(): MutableList<ProductsWithRelatedVariants>
}

TheDatabase the @Database annotated class so demo can be run :-

@TypeConverters(Converters::class)
@Database(entities = [Products::class,Variants::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries() /* for convenience brevity run on the main thread */
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

MainActivity putting the above into action:-

const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val productId=100
        var vm1 = mutableListOf<Variants>(
            Variants(measurement = "10 inches", discounted_price = "11.99", cart_count = 10, product_id = 100),
            Variants(measurement = "10 ounces", discounted_price = "2.50", cart_count = 5, product_id = 100),
            Variants(measurement = "100 grams", discounted_price = "1.75", cart_count = 3, product_id = 100)
        )
        dao.insert(Products(100, name = "Product1",VariantsMutableListHolder(vm1)))
        /* Insert the related variants */
        val insertedVariantIdList: ArrayList<Long> = ArrayList(0)
        for (v in vm1) {
            insertedVariantIdList.add(dao.insert(v))
        }
        /* Update the 2nd Variants */
        dao.updateVariant(insertedVariantIdList[1].toInt(),"99",productId)

        /* Used for building output data (both)*/
        val sb = StringBuilder()

        /*STG001*/
        /* Extract data just stored in the Product table */
        for(p in dao.getAllProducts()) {
            sb.clear()
            for (v in p.variantsMutableListHolder.variantsMutableList) {
                sb.append("\n\tMSR=${v.measurement} DP=${v.discounted_price} CC=${v.cart_count}")
            }
            Log.d(TAG+"+STG001","PRODUCT NAME IS ${p.name} it has ${p.variantsMutableListHolder.variantsMutableList.size} variants; they are:-$sb")

        }
        /*STG002*/
        /* Extract the data from the Product Table along with the related variants i.e. ProductsWithRelatedVariants */
        for(pwrv in dao.getAllProductsWithTheRelatedVariants()) {
            sb.clear()
            for (v in pwrv.variantsList) {
                sb.append("\n\tMSR=${v.measurement} DP=${v.discounted_price} CC=${v.cart_count}")
            }
            Log.d(TAG+"+STG002","PRODUCT NAME IS ${pwrv.products.name} it has ${pwrv.products.variantsMutableListHolder.variantsMutableList.size} variants; they are:-$sb")
        }

    }
}

So when run (note only meant to be run once to demo):-

1. Sets the Product id to be used to 100
   1. just one product needed to demo
   2. 100 could be any value, just 100 is easy and demonstrates setting a specific id.
2. Builds a MutableList as the Variants data to both be stored as part of the Product (the suggested bit to skip) and as rows in the Variant table.
   1. note id's are irrelevant here if as suggested storing Variants in the variants table
3. Inserts the Product along with the Variants in the Product table.
4. Loops through the MutableList inserting each Variants into the Variant table, the id being generated and stored in the ArrayList. The product_id being set to the Product row that was inserted.
   1. Ideally each insert should be checked, as if the returned id is -1 then the row has not been inserted due to a conflict (beyond the scope of this question though).
      1. NOTE if a ForeignKey conflict (e.g. 99 given as the product_id not 100) then App would fail (beyond the scope of the question to go into handling that situation).
5. UPDATE one of the cart_counts (2nd variant changed to 99)
6. retrieve all the products and output the product details and the Variants stored in the Product table details (i.e. cart_count NOT changed)
7. retrieve all the products with the related variants from the variant table as a list of ProductsWithRelatedVariants (could be a Flow).

Result (as output to the Log) :-

D/DBINFO+STG001: PRODUCT NAME IS Product1 it has 3 variants; they are:-
        MSR=10 inches DP=11.99 CC=10
        MSR=10 ounces DP=2.50 CC=5
        MSR=100 grams DP=1.75 CC=3


D/DBINFO+STG002: PRODUCT NAME IS Product1 it has 3 variants; they are:-
        MSR=10 inches DP=11.99 CC=10
        MSR=10 ounces DP=2.50 CC=99
        MSR=100 grams DP=1.75 CC=3

As can be seen, the near exact same data is extracted BUT CC=5 is still stored in the Product table, whilst the in the variants second and suggested way, CC=99.

Furthermore, have a look at the Data in the database:-

• noting the highlighted bloated (unnecessary data) as opposed to the same data in the Variant table:-

An Alternative

The alternative would be to Update the Product Table, here's a risky example of how:-

UPDATE Product SET variants = substr(variants,1,instr(variants,'"cart_count":5,"'))||'"cart_count":99,"'||substr(variants,instr(variants,'"cart_count":5,"') + length('"cart_count":5,"')) WHERE id=100 AND instr(variants,'"cart_count":5,"');

• Note App Inspection took an exception to SQLite's replace function, so instead the substr and instr functions were used. NOTE this query has not been tested to cope with all scenarios and may well result in unpredictable results, it would not cope with the column name being changed as an example.

After running the query then the Product table has :-

• Oooops looks like I got too many double quotes somewhere (good example of how flimsy this approach would be).

Suggested Solution

Here's the suggested solution with the code trimmed down to just what is needed.

@Entity(tableName = "Product")
data class Products (
    @PrimaryKey
    var id : Int? = null, /* allows generation of the id, if need be (should really be Long though )*/
    var name  : String? = null,
)

@Entity(
    tableName = "Variant",
    /* You may wish to consider adding Foreign Key constraints */
    /*  FK constraints enforce referential integrity*/
    foreignKeys = [
        ForeignKey(
            entity = Products::class, /* The Parent Class */
            parentColumns = ["id"], /* The column or columns (if composite key) that map to the parent */
            childColumns = ["product_id"], /* the column or columns in the child that reference the parent */
            /* Optional but assists in maintaining referential integrity automatically */
            onDelete = ForeignKey.CASCADE, /* if a parent is deleted then so are the children */
            onUpdate = ForeignKey.CASCADE /* if the reference column in the parent is changed then the value in the children is changed */
        )
    ]
)
data class Variants (

    @PrimaryKey
    var id : Int?  = null, /* allows generation of id, if null passed */
    @ColumnInfo(index = true)
    var product_id : Int?  = null,
    var measurement : String?  = null,
    var discounted_price : String?  = null,
    var cart_count : Int?  = null
)
/* Class for retrieving the Product with the children from the Variant table */
data class ProductsWithRelatedVariants(
    @Embedded
    var products: Products,
    @Relation(
        entity = Variants::class, /* The class of the Children */
        parentColumn = "id", /* the column in the parent table that is referenced */
        entityColumn = "product_id" /* The column in the child that references the parent*/
    )
    var variantsList: List<Variants>
)
@Dao
interface AllDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(products: Products): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(variants: Variants): Long
    /* adjusted to suit code in question */
    @Query("UPDATE Variant SET cart_count= :cart_count WHERE id = :id")
    fun updateVariant(id: Int, cart_count: String) : Int
    @Query("SELECT * FROM Variant WHERE id=:id")
    fun getVariantsById(id: Int): Variants
    /* adjusted to suit code in question */
    @Transaction
    @Query("SELECT * FROM Product /*WHERE subcategory_id=:subcatid*/")
    fun getAllProducts(): /*Flow<*/MutableList<Products>/*>*/ /*As run on main thread no flow needed */
    @Transaction
    @Query("SELECT * FROM Product")
    fun getAllProductsWithTheRelatedVariants(): MutableList<ProductsWithRelatedVariants>
}
@Database(entities = [Products::class,Variants::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries() /* for convenience brevity run on the main thread */
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

And to test:-

const val TAG = "DBINFO"
class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()

        val productId=100
        var vm1 = mutableListOf<Variants>(
            Variants(measurement = "10 inches", discounted_price = "11.99", cart_count = 10, product_id = 100),
            Variants(measurement = "10 ounces", discounted_price = "2.50", cart_count = 5, product_id = 100),
            Variants(measurement = "100 grams", discounted_price = "1.75", cart_count = 3, product_id = 100)
        )
        dao.insert(Products(100, name = "Product1"))
        /* Insert the related variants */
        val insertedVariantIdList: ArrayList<Long> = ArrayList(0)
        for (v in vm1) {
            v.product_id = productId
            insertedVariantIdList.add(dao.insert(v))
        }
        /* Update the 2nd Variants */
        dao.updateVariant(insertedVariantIdList[1].toInt(),"99")

        /* Used for building output data (both)*/
        val sb = StringBuilder()
        /*STG002*/
        /* Extract the data from the Product Table along with the related variants i.e. ProductsWithRelatedVariants */
        for(pwrv in dao.getAllProductsWithTheRelatedVariants()) {
            sb.clear()
            for (v in pwrv.variantsList) {
                sb.append("\n\tMSR=${v.measurement} DP=${v.discounted_price} CC=${v.cart_count}")
            }
            Log.d(TAG+"+STG002","PRODUCT NAME IS ${pwrv.products.name} it has ${pwrv.variantsList.size} variants; they are:-$sb")
        }
    }
}

And the Result in the log (using the trimmed code):-

D/DBINFO+STG002: PRODUCT NAME IS Product1 it has 3 variants; they are:-
        MSR=10 inches DP=11.99 CC=10
        MSR=10 ounces DP=2.50 CC=99
        MSR=100 grams DP=1.75 CC=3