How to create M:N Relationship with Room and Kotlin
In this article i will explain how to create a relationship Many to Many using Room. I’ll try to be more practice as possible, without writing a lot of lines of theory but just the necessary. I will follow these points :
- Description of Many to Many Relationship
- Creating the Room Entity for it
- Inserting a relationship
- Querying the relationship data
For this purpose i will use the following Entities : Recipe and Day, where a Recipe could be assigned to multiple days, and a Day could contain multiple recipes. This sample is something that I’m doing for an application of recipes where the user can create a meal plan for the week.
The standard way to represent a Many to Many relationship is through the use of a JOIN TABLE. You can imagine this table as a table between two entities :
You can see that Tuesday has multiple Recipes, so in this way we are creating a Many to Many relationship. I’ve used the field Name just for explaining the aim of this table, in reality what we store here is the dayId and the recipeId
Let’s see how the Room Entities look for these
@Entity(primaryKeys = ["id"])
data class Recipe(val id: Int, val name : String)@Entity(primaryKeys = ["id"])
data class Day (val id : Int , val name : String)
The first thing that we see here is that we don’t need to specify a ForeignKey, but the we need to create an Entity to represent AssignedRecipe. For this purpose I’m going to store it in a class called DatabaseDataHolder as we need a few other things before we are finished.
class DatabaseDataHolder {
@Entity(
primaryKeys = ["day", "recipe"], foreignKeys = [
ForeignKey(
entity = Day::class,
parentColumns = ["id"],
childColumns = ["day"],
onDelete = ForeignKey.CASCADE
),
ForeignKey(
entity = Recipe::class,
parentColumns = ["id"],
childColumns = ["recipe"],
onDelete = ForeignKey.CASCADE
)]
)
class AssignedRecipe {
var day: Int = 0
var recipe: Int = 0
}
}Inside this table we have two Int fields, one for the day and the other one for the recipe. These fields are used as ForeignKey for dayId and recipeId.
Remember that you need to add this table to your Entities list in your Database class.
Now that we have defined a new entity and made the database aware of it, we need to define a function to assign a Recipe. So let’s define a Dao called RecipeDao
@Dao
interface RecipeDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun addAssignedRecipe( assignedRecipe: AssignedRecipe)
}I’m using the OnConflictStrategy.IGNORE to avoid to insert an AssignedRecipe which already exists with the same recipe and day.
It’s simple to use this function :
thread {
var assignedRecipe = DatabaseDataHolder.AssignedRecipe()
assignedRecipe.day = day
assignedRecipe.recipe = recipe
recipeDao.addAssignedRecipe(assignedRecipe)
}To un-assign a Recipe we delete all the rows in the AssignedRecipe table with that recipeId/dayId, so always inside RecipeDao we add this function
@Query("DELETE FROM AssignedRecipe WHERE AssignedRecipe.day =:dayId")
fun removeAllAssignedRecipeByDayId( dayId : Int)@Query("DELETE FROM AssignedRecipe WHERE AssignedRecipe.recipe =:recipeId")
fun removeAllAssignedRecipeByRecipeId( recipeId : Int)
At this point we have our main tables, but what we really want is to obtain all the data from Recipe and Day, for example we want to know all the recipes for a specific day. A good way to represent these info could be a Pair, like <1, 4> or <Monday , Pasta>. We need to join across 3 tables (Day , AssignedRecipe, Recipe) in order to obtain all the data that we need
Let’s see how to create this result through a query, defining a class which represents the Pair<Day, Recipe> as first
class DatabaseDataHolder {
@Entity(
primaryKeys = ["day", "recipe"], foreignKeys = [
ForeignKey(
entity = Day::class,
parentColumns = ["id"],
childColumns = ["day"],
onDelete = ForeignKey.CASCADE
),
ForeignKey(
entity = Recipe::class,
parentColumns = ["id"],
childColumns = ["recipe"],
onDelete = ForeignKey.CASCADE
)]
)
class AssignedRecipe {
var day: Int = 0
var recipe: Int = 0
}
class RecipeDayPair {
@Embedded(prefix = "recipe_")
lateinit var recipe : Recipe
@Embedded
var day : Day? = null
}}
We know that a Recipe would not be null, so i am using lateinit to avoid make a recipe nullable. Then I’m using Embedded annotation to avoid conflicts with the fields of the other tables. Now we can define the query which returns LiveData<List<DatabaseDataHolder.RecipeDayPair>>. Let’s divide it into different parts just for make it more simple
We select some fields from Recipe table, everything from Day table and then AssignedRecipe.day. I prefix Recipe’s fields with “recipe_nameField”otherwise we have some conflicts with column names of Day.
SELECT Recipe.id as recipe_id, Recipe.name as recipe_name, AssignedRecipe.day , Day.*Then we start to join the tables (We’re trying to replicate the Join Table that i have explained in the last picture) :
- We join Recipe table with AssignedRecipe using the Recipe.id
FROM Recipe LEFT OUTER JOIN AssignedRecipe on AssignedRecipe.Recipe = Recipe.id- We join Day table using the Day.id
LEFT OUTER JOIN Day on AssignedRecipe.day = Day.idThen we put all these parts together and we have our query
@Query("SELECT Recipe.id as recipe_id, Recipe.name as recipe_name, AssignedRecipe.day , Day.* FROM Recipe LEFT OUTER JOIN AssignedRecipe on AssignedRecipe.Recipe = Recipe.id LEFT OUTER JOIN Day on AssignedRecipe.day = Day.id")
fun getAllWithAssignedDays() : LiveData<List<DatabaseDataHolder.RecipeDayPair>>If a Recipe is unassigned we expect that Day will be null for that Recipe.
So with this query we can obtain a list of Pair<Day, Recipe>, for example :
{ Pair(Monday , Pasta) , Pair(Tuesday, Chicken Fajitas) ,
Pair(Tuesday , Pasta) }This result is ok but i think we can do it better and use something more handy than a list of Pair. I’d like to have a Day and a list of Recipes for that day, so we can try to transform our List of Pair objects into this new representation. Like we have done previously with RecipeDayPair, let’s create inside a DatabaseDataHolder a class which represents a Day and a list of Recipes.
data class DayAndItsRecipes (val day: Day,
val recipes: List<Recipe>)Now let’s use the query that we have declared previously and we take the response which will be a list of RecipeDayPair
viewModel.getAllWithAssignedDays().observe( this , Observer { response ->
if(!response.isNullOrEmpty()) {
}
})
Now we want to transform this list in something that is more easy to work with, like a list of DayAndItsRecipes. For this purpose I’m going to group each item by Day, using the function of Kotlin groupBy. This function gives a Map<Day, List<Recipe>>.
We iterate through this Map using the function forEach, and populate our list
combinedRecipes.forEach { items.add(DatabaseDataHolder.DayAndItsRecipes(it.key , it.value))}That’s it, we have our list of DayAndItsRecipes. An important thing is to make the code readable and reusable, so it’s better to move this last part of code inside DatabaseDataHolder class and make it reusable everywhere.
We can use this function everywhere and it makes the code more readable :)
It’s not a simple topic, i know, and I’ve not found good tutorials on Google. I hope that this article will help you, despite my english, and if you have some questions feel free to contact me on Linkedin.
