Delete All Values in One to Many Relationship Room Kotlin

Question

I want to delete all values inside one to many relationship

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 = "category_id")
  var category_id : String? = null,  
  @ColumnInfo(name = "subcategory_id")
  var subcategory_id : String? = null,  
  @ColumnInfo(name = "other_images")
  var other_images: List<String>  = listOf(),  
  @ColumnInfo(name = "price")
  var price : String? = null,  
  @ColumnInfo(name = "variants")
  var variants : List<Variants> = listOf()  
)

Child Table :

@Entity(tableName = "Variant")  
data class Variants (  
  @PrimaryKey(autoGenerate = false)  
  @ColumnInfo(name = "id")
  var id : Int  = 0,  
  @ColumnInfo(name = "product_id")
  var product_id : String?  = null,  
  @ColumnInfo(name = "price")
  var price : String?  = null,   
  @ColumnInfo(name = "discounted_price")
  var discounted_price : String?  = null,   
  @ColumnInfo(name = "image")
  var image : String?  = null,   
  @ColumnInfo(name = "moq")
  var moq : String?  = null,   
  @ColumnInfo(name = "cart_count")
  var cart_count : String?  = null,   
  @ColumnInfo(name = "is_notify_me")
  var is_notify_me : Boolean? = null             
)

Relationship :

data class ProductWithVariants(  
    @Embedded val product: Products,   
    @Relation(   
        parentColumn = "id",   
        entityColumn = "id"    
    )    
    val variants: MutableList<Variants>   
)

Simply.. i want to delete all products and variants , Delete Single Product with corresponding variants and Update Single Product

Any changes in relationship ?? and i also need queries in dao !!

Masoud Karimi · Accepted Answer · 2022-09-03T07:38:25.183

You should use ForeignKey to specify relation between Entities.

As the documentation says:

Foreign keys allow you to specify constraints across Entities such that SQLite will ensure that the relationship is valid when you modify the database.

To add foreign key, do this:

Parent class

@Entity
data class User(
    @PrimaryKey
    val userId: Int,
    val name: String
)

Child class:

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = arrayOf("userId"),
            childColumns = arrayOf("ownerUserId"),
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class Pet(
    @PrimaryKey
    val petId: Int,
    val name: String,
    @ColumnInfo(index = true)
    val ownerUserId: Int
)

Define relation between objects:

data class UserWithPets (
    @Embedded
    val user: User,
    @Relation(
        parentColumn = "userId",
        entityColumn = "ownerUserId"
    )
    val pets: List<Pet>
)

Create a DAO:

@Dao
interface UserDao {
    @Insert
    suspend fun saveUser(user: User)

    @Insert
    suspend fun saveUsers(users: List<User>)

    @Insert
    suspend fun savePets(pets: List<Pet>)

    @Query("SELECT * FROM USER")
    suspend fun getUsers(): List<UserWithPets>

    @Query("DELETE FROM USER WHERE userId=:id")
    suspend fun deleteUser(id: Int)

    @Query("DELETE FROM USER")
    suspend fun deleteAllUsers()

    @Update
    suspend fun updatePet(pet: Pet)
}

Some example queries:

// Add new user
 val user = User(userId = 1, name = "User1")
 userDao.saveUser(user)

 var userWithPets = userDao.getUsers()
// result -> UserWithPets(user=User(userId=1, name=User1), pets=[])

// Add new pet
 val pet = Pet(petId = 1, ownerUserId = 1, name = "Pet Name")
 userDao.savePets(listOf(pet))

// Fetch users again
//UserWithPets(user=User(userId=1, name=User1), pets=[Pet(petId=1, name=Pet Name, ownerUserId=1)])

 
// Update pet
userDao.updatePet(pet.copy(name = "New Name"))

// Fetch users again
//UserWithPets(user=User(userId=1, name=User1), pets=[Pet(petId=1, name=New Name, ownerUserId=1)])

This way, whenever you delete a User, all its Pets will delete automatically.

thank you so much .. please tell me what if i want to delete all user and its pets @Masoud Karimi — SasidharanIOS, Sep 03 '22 at 07:01
I edited the answer. If you call the `deleteAllUsers()`, then all the users and pets will delete. Because of the `onDelete = ForeignKey.CASCADE`, the cascade action propagates the delete or update operation on the parent key to each dependent child key. — Masoud Karimi, Sep 03 '22 at 07:11
Thank you so much .. will you please tell me update query to update the pet name @Masoud Karimi — SasidharanIOS, Sep 03 '22 at 07:13
Hi Masoud Karimi , in insert query i use (onConflict = OnConflictStrategy.REPLACE) to replace the old list and insert new list even if the primary key is not same for both old and new list ..but it doesn't work ..please share your answer for this @ Masoud Karimi — SasidharanIOS, Sep 04 '22 at 02:12
Actually, `onConfilict` strategy affects when you add a new item that already existed in the database(i.e. the primary key exists). In this scenario, the old item replaces with the new item. If the primary keys are different, the new item will add to the DB without a conflict. — Masoud Karimi, Sep 04 '22 at 04:55
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/247767/discussion-between-sasidharanios-and-masoud-karimi). — SasidharanIOS, Sep 04 '22 at 04:57

Delete All Values in One to Many Relationship Room Kotlin

1 Answers1