Room - Relation

Model one-to-many and many-to-many relationships in Room using @ForeignKey and retrieve related data with @Relation (and @Junction)

• Introduction
• One-to-Many Relations
• Foreign Keys
• Retrieving the Related Entities
• Many-to-Many Relations
• Tasks
• Pending

Introduction

Room fully supports foreign key relationships via @ForeignKey. This sets up the foreign keys in the appropriate tables.

Room also has a @Relation annotation to retrieve related data, though it does not directly add navigation properties to your entities.

One-to-Many Relations

Let’s imagine an app for a book catalog.

The catalog is divided into categories, and categories can have books.

We need a Category entity to model categories and a Book entity to model books, along with a one-to-many relationship between Category and Book.

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

@Entity(tableName = "category")
data class Category(
    @PrimaryKey
    val code: String,
    val name: String
)

Note that it does not have a List or other collection of Book objects.

You cannot ask a category for its books by navigating from the entity itself.

Foreign Keys

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 Category:

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

We declare an index on category, and, as the name suggests, this holds the code 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.

The @Entity annotation also has an array of @ForeignKey annotations (foreignKeys).

Each @ForeignKey annotation has at least three primary properties:

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

Cascades on Updates and Deletes

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 is a popular choice for onDelete.

In the Book entity’s @ForeignKey, we use onDelete = CASCADE, so if a Category is deleted, all of its associated Book rows are deleted from the book table.

Retrieving the Related Entities

For many operations, our DAO is no different from others: we can insert, update, delete, and query entities.

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 code.

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.

A @Query does not have to return entities. It can return any type that Room can map from the query result. 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 = "code",
        entityColumn = "category"
    )
    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 based around Book, Room knows that it needs to query the book table. The parentColumn and entityColumn properties teach Room how to map data from our direct query result to Book.

Specifically, Room should:

• Get the value of code for a Category returned by the @Query, and
• Query the book table to find all rows where category matches that code value

We can then use CategoryAndBooks in @Query functions:

@Entity(tableName = "category")
data class Category(
    @PrimaryKey
    val code: String,
    val name: String
) {
    @Dao
    interface SQL {
        @Insert
        suspend fun insert(category: Category)

        @Transaction
        @Query("select * from category where code = :code")
        suspend fun selectWhereCode(code: String): CategoryAndBooks?
    }
}

The selectWhereCode function returns a CategoryAndBooks object, so we get a category and its associated books.

Because Room performs multiple queries to hydrate relations, annotate SQL functions returning relation-containing data classes with @Transaction to avoid inconsistent snapshots.

We can then do things like create categories and books, save those to the database and retrieve them later:

    @Test
    fun test() = runTest {

        val db = Room.inMemoryDatabaseBuilder<Database>().setDriver(BundledSQLiteDriver()).build()

        val category = Category(code = "FIC", name = "Fiction")
        val book1 = Book(isbn = "978-0-123456-78-9", title = "The Great Adventure", category = "FIC")
        val book2 = Book(isbn = "978-0-987654-32-1", title = "Mystery of the Lost City", category = "FIC")

        db.category().insert(category)
        db.book().insert(book1, book2)

        val categoryAndBooks = db.category().selectWhereCode("FIC")!!
        categoryAndBooks.category shouldBe category
        categoryAndBooks.books shouldBe listOf(book1, book2)

        db.category().selectWhereCode("NON") shouldBe null

        db.close()
    }

Many-to-Many Relations

For many-to-many, use a join table entity.

@Relation supports this via associateBy = @Junction.

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:

@Entity(tableName = "category"")
data class Category(
  @PrimaryKey
  val code: String,
  val name: String
)

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

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

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

@Entity(
  primaryKeys = ["isbn", "code"],
  indices = [Index("isbn"), Index("code")],
  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 BookCategory(
  val isbn: String,
  val code: String
)

The BookCategory class has our keys: isbn to point to a Book and code to point to a Category.

Hence, each BookCategory 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 code 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

The @Relation annotation in CategoryBook has a new property, associateBy, that contains a @Junction annotation:

data class CategoryBook(
  @Embedded
  val category: Category,
  @Relation(
    parentColumn = "code",
    entityColumn = "isbn",
    associateBy = Junction(
      value = BookCategory::class,
      parentColumn = "code",
      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 BookCategory 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.

Tasks

Pending

• Android - Relationships
• Database Relations with Room