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 !!

Answer 1

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:

1. Parent class

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

2. 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
)

3. Define relation between objects:

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

4. 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.