Many-to-Many Relations

We can model one-to-many relations by having the “many” side (e.g., Book) have a foreign key back to its corresponding “one” item (e.g., Category).

The traditional way to model many-to-many relations is through a “join table”, where rows in that table represent the pairs of entities that are related. To change the relationships, you add or remove join table rows.

That is how Room handles many-to-many relations, with the assistance of a @Junction annotation.

Declaring the Join Table

Suppose that we want to say that a Book can be in more than one Category, such as “Android Programming Books” and “Books Written By Balding Men”. That now means that Book and Category have a many-to-many relationship, as we still want a Category to have many Book objects.

Our Category does not need to change:

package com.commonsware.room.misc.manytomany

import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "categoriesManyToMany")
data class Category(
  @PrimaryKey
  val shortCode: String,
  val displayName: String
)

Our Book no longer needs categoryShortCode, as that can only model a one-to-many relationship:

package com.commonsware.room.misc.manytomany

import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "booksManyToMany")
data class Book(
  @PrimaryKey
  val isbn: String,
  val title: String
)

However, we now need a third entity, to model the join table (here called BookCategoryJoin):

package com.commonsware.room.misc.manytomany

import androidx.room.Entity
import androidx.room.ForeignKey
import androidx.room.ForeignKey.CASCADE
import androidx.room.Index
import androidx.room.OnConflictStrategy

@Entity(
  primaryKeys = ["isbn", "shortCode"],
  indices = [Index("isbn"), Index("shortCode")],
  foreignKeys = [
    ForeignKey(
      entity = Book::class,
      parentColumns = arrayOf("isbn"),
      childColumns = arrayOf("isbn"),
      onDelete = CASCADE
    ), ForeignKey(
      entity = Category::class,
      parentColumns = arrayOf("shortCode"),
      childColumns = arrayOf("shortCode"),
      onDelete = CASCADE
    )
  ]
)
data class BookCategoryJoin(
  val isbn: String,
  val shortCode: String
)

The BookCategoryJoin class has our keys: isbn to point to a Book and shortCode to point to a Category. Hence, each BookCategoryJoin instance (or row in its table) represents one relationship between a Book and Category.

The @Entity annotation is more complex:

• We use the primaryKeys property to say that the combination of isbn and shortCode is the primary key for our table
• We set up indices on each of those columns, as we will be querying this table a lot to find all categories for a book or all books for a category
• We have two @ForeignKey annotations, tying this class to Book and Category, and using onDelete = CASCADE to ensure that when we delete a Book or Category that its corresponding join entry gets deleted

Retrieving the Related Entities

In Bookstore, the @Relation annotation in CategoryAndBooks has a new property, associateBy, that contains a @Junction annotation:

data class CategoryAndBooks(
  @Embedded
  val category: Category,
  @Relation(
    parentColumn = "shortCode",
    entityColumn = "isbn",
    associateBy = Junction(
      value = BookCategoryJoin::class,
      parentColumn = "shortCode",
      entityColumn = "isbn"
    )
  )
  val books: List<Book>
)

This teaches the @Relation about our join table and how to map columns in the query’s result set to columns in the join table. This allows Room to be able to retrieve the books for a category. And, if we wanted, we could create a BookAndCategories class that handled the opposite case, wrapping a Book and a list of its associated Category objects.

We can then use CategoryAndBooks in our DAO functions:

  @Transaction
  @Query("SELECT * FROM categoriesManyToMany")
  abstract suspend fun loadAll(): List<CategoryAndBooks>

  @Transaction
  @Query("SELECT * FROM categoriesManyToMany WHERE shortCode = :shortCode")
  abstract suspend fun loadByShortCode(shortCode: String): CategoryAndBooks

The @Transaction annotations are there because we will wind up with multiple queries to populate our CategoryAndBooks objects, and Room will not automatically set up a database transaction for us to ensure that those queries all work off of the same edition of the data.