One-to-Many Relations

Let’s imagine that we are setting up an app for a book catalog. The catalog is divided into categories, and categories can have books. So, we need a Category entity to model the categories, and we need a Book entity to model the books. As part of this, we also need to model the one-to-many relationship between Category and Book.

In this case, Category itself does not need anything special. It is just an ordinary Room entity:

package com.commonsware.room.misc.onetomany

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

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

Note, though, that it does not have a List or Array or other collection of Book objects. You cannot ask a category for its books, at least not by asking the entity.

Configuring the Foreign Key

Book, and our DAO, are where things start to get interesting.

The Book class, in isolation, is about as plain as is Category:

data class Book(
  @PrimaryKey
  val isbn: String,
  val title: String,
  @ColumnInfo(index = true) var categoryShortCode: String
)

The only thing interesting here is that we declare an index on categoryShortCode. As the name suggests, this holds the shortCode primary key of the Category that is associated with this Book. Note that Book does not have an actual property for the Category, just its key.

When we scroll up the source code a bit and look at the @Entity annotation, we encounter a @ForeignKey:

package com.commonsware.room.misc.onetomany

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.ForeignKey
import androidx.room.ForeignKey.CASCADE
import androidx.room.PrimaryKey

@Entity(
  tableName = "books",
  foreignKeys = [ForeignKey(
    entity = Category::class,
    parentColumns = arrayOf("shortCode"),
    childColumns = arrayOf("categoryShortCode"),
    onDelete = CASCADE
  )]
)
data class Book(
  @PrimaryKey
  val isbn: String,
  val title: String,
  @ColumnInfo(index = true) var categoryShortCode: String
)

The @Entity annotation can have an array of @ForeignKey annotations (named foreignKeys). Each @ForeignKey annotation has at least three primary properties:

• entity points to the entity class that represents the “one” side of the one-to-many relation
• parentColumns identifies the column(s) in the parent table that represent the primary key
• childColumns identifies the column(s) in the child table that represent the parent’s primary key

In this case, Category has a simple single-property primary key, so parentColumns points to that (shortCode), while childColumns points to the corresponding column in the Book (categoryShortCode).

Cascades on Updates and Deletes

In addition, you can place onUpdate and onDelete properties on a @ForeignKey annotation. These indicate what actions should be taken on this entity when the parent of the foreign key relationship is updated or deleted. There are five possibilities, denoted by ForeignKey constants:

NO_ACTION is the default, though CASCADE will be a popular choice for onDelete. In fact, we use CASCADE for onDelete in the Book entity’s @ForeignKey, so if the Category is deleted, all of its associated Book rows are deleted from the books table.

Retrieving the Related Entities

For many things, our DAO can be no different than any other one we have seen so far. We can insert, update, delete, and query our entities as we see fit. For example, our Bookstore DAO has two @Insert functions to save categories and books:

  @Insert
  suspend fun save(category: Category)

  @Insert
  suspend fun save(vararg books: Book)

However, from a property standpoint, books and categories only have keys, not references to other entities. So, by default, if we have a @Query function that returns a Book, we have to execute a separate @Query function to look up its Category via the shortCode. And if we have a @Query function that returns a Category, we have to have another @Query function to retrieve all books associated with that Category.

To work around this limitation to some extent, we can use @Relation.

A @Query does not have to return entities. As we saw earlier, it can return other things, such as an Int result from an aggregate function. So long as Room can figure out how to map the columns in your query result to properties of some return type, Room is happy.

So, we can declare a custom data class for a @Query response, such as this CategoryAndBooks class:

data class CategoryAndBooks(
  @Embedded
  val category: Category,
  @Relation(
    parentColumn = "shortCode",
    entityColumn = "categoryShortCode"
  )
  val books: List<Book>
)

By using @Embedded, any columns that we return from the @Query that can go in a Category will go into the category property.

The @Relation annotation says that, in addition to processing our direct query (from a @Query annotation), Room should automatically make a second query to retrieve related entities. Since our @Relation is tied to a property that is based around Book, Room knows that it needs to query our books table. The parentColumn and entityColumn annotation properties teach Room how to map data from our direct query result to Book. Specifically, Room should:

• Get the value of shortCode for a Category returned by the @Query, and
• Query the books table to find all rows where categoryShortCode matches that shortCode value

We can then use CategoryAndBooks in @Query functions:

package com.commonsware.room.misc.onetomany

import androidx.room.*

data class CategoryAndBooks(
  @Embedded
  val category: Category,
  @Relation(
    parentColumn = "shortCode",
    entityColumn = "categoryShortCode"
  )
  val books: List<Book>
)

@Dao
interface Bookstore {
  @Insert
  suspend fun save(category: Category)

  @Insert
  suspend fun save(vararg books: Book)

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

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

The loadAll() and loadByShortCode() functions return CategoryAndBooks objects, so we get categories and their associated books. Because Room needs to perform multiple queries for this, we use the @Transaction annotation to ensure that Room does all of those SQL calls inside a transaction.

We can then do things like create categories and books:

    val category =
      Category(shortCode = "stuff", displayName = "Books About Stuff")
    val bookOne = Book(
      isbn = "035650056X",
      title = "Feed",
      categoryShortCode = category.shortCode
    )
    val bookTwo = Book(
      isbn = "0451459792",
      title = "Dies the Fire",
      categoryShortCode = category.shortCode
    )

…save those to the database:

      underTest.save(category)
      underTest.save(bookOne, bookTwo)

…and retrieve them later:

      val all = underTest.loadAll()

      assertThat(all, hasSize(equalTo(1)))
      assertThat(all[0].category, equalTo(category))
      assertThat(
        all[0].books,
        allOf(hasSize(equalTo(2)), hasElement(bookOne), hasElement(bookTwo))
      )

      val loaded = underTest.loadByShortCode(category.shortCode)

      assertThat(loaded.category, equalTo(category))
      assertThat(
        loaded.books,
        allOf(hasSize(equalTo(2)), hasElement(bookOne), hasElement(bookTwo))
      )

Representing No Relation

Sometimes, with one-to-many relations, the more correct model is “zero/one-to-many”. For example, perhaps a Book has not yet been assigned to a Category.

For that, simply make the foreign key property (e.g., categoryShortCode) be nullable, and let null represent the lack of a relationship.