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 !!
CodePudding user response:
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.