Many to many relationship CRUD in Room

user_12035648 0
Viewed 410

I have modeled a many-to-many relationship with Room based on the docs and this medium article. Using this @Relation I can retrieve RecipeWithIngredients or IngredientWithRecipe Objects from the database. For this to work I need to insert a recipe to the recipe table, an ingredient to the ingredient table then a reference with both of their ids to the reference table first. I don't think there is another way of doing insertion other than using @Transaction on the Dao and just doing it all on one method, however for deletion and updating shouldn't I be able to do those using the Relation as well? The documentation on Room looks a bit lacking to me and so I've come to you

enter image description here

//Get for recipeWithIngredients object. (This works)
@Transaction
@Query("SELECT * FROM Recipe WHERE recipeID = :ID")
suspend fun getRecipeWithIngredients(ID:Long): RecipeWithIngredients

//Insert recipeWithIngredients object. (I don't see why this wouldn't work)
//IDs are autoGenerated so I can't get them until they are inserted

@Transaction 
suspend fun insertRecipeWithIngredients(recipeWithIngredients: RecipeWithIngredients){
    val recipeID = insertRecipe(recipeWithIngredients.recipe)

    for(ingredient in recipeWithIngredients.ingredients){
        val ingredientID = insertIngredient(ingredient)
        insertRecipeIngredientRef(recipeID, ingredientID )

    }
}

//I could try doing this like the insert but should't there be a way to do it like the get?
@Transaction
@Query("SOME SQL HERE")
suspend fun deleteRecipeWithIngredients(recipeWithIngredients: RecipeWithIngredients)
android-room android
1 Answers

I don't think there is another way of doing insertion other than using @Transaction on the Dao and just doing it all on one method

Your idea implemented by insertRecipeWithIngredients is interesting and I guess it should work, but I wouldn't say it's a common way to do that. Many-to-many relation in general - is when you have two separate entities that have a value even without relationship between them (User & UserGroup or for example). Even in your use-case it can be that one user at first adds recipe with its short text description and needed steps then another user binds ingredients to it, so insertRecipe and insertRecipeIngredients should be done separately. In this schema you should implement three separate insert methods - first for Recipe, second for Ingridient and third - for RecipeIngridientRef. But you of course in your specific case you can do like you've described above.

for deletion and updating shouldn't I be able to do those using the Relation as well

Out-of-the box Room doesn't support that. It's only for queries.

If for deletion you want to delete rows from RecipeIngridientRef that are referenced to specific Recipe, than it would be enough to use Foreign Key standard mechanism - Cascade deleting. With that you need only to write delete for Recipe entity and the rest Room&Sqlite will do for you.

As for update it's really lack of recommendations in documentation. One of the choices - is first to delete all rows in RecipeIngridientRef with specific Recipe and then to use the same method insert for RecipeIngridientRef (both operations in transaction).

user_127184140
Related