Performing multiple joins with the Room persistence library.

A while back, I was working on an Android application when I had the need to query the local SQLite database for data present on 3 separate tables.

The android documentation on defining relationships between objects does a great job of explaining how to write Room queries for the different relationships that can exist between two tables i.e one-to-one, one-to-many, and many-to-many. But it wasn’t initially clear how I was going to write a query to fetch data from more than 2 tables. After some online searching combined with multiple re-reads of the Room documentation, here’s what I was able to implement.

Setting the Stage

The project is a Q&A application that displays a list of questions with corresponding options for the user to choose from. It also stores the user-selected option so it can highlight the selected option if the user were to revisit the question/reopen the app at a later time. The database consists of a questions table that contains the actual question text; an options table that contains the choices that a given question would have the user select from; and finally, a users_answers table that stores the option a user selects for any given question. So each question has multiple options but one single user-selected answer. Here’s what the Room entities look like (edited for brevity):

// The questions table
@Entity(tableName="questions")
data class QuestionLocal(
    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    val id: String,

    @ColumnInfo(name = "text")
    val text: String
)

// The options table
@Entity(tableName="options")
data class QuestionLocal(
    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "id")
    val id: String,

    @ColumnInfo(name = "text")
    val text: String,

    @ColumnInfo(name = "question_id")
    val questionId: String
)

// The user_options table
@Entity(tableName="user_answers")
data class UserAnswerLocal(
    @PrimaryKey(autoGenerate = false)
    @ColumnInfo(name = "question_id")
    val questionId: String,

    @ColumnInfo(name = "option_id")
    val optionId: String
)

The goal is to query the questions table and for each question return the related options and user-selected answer. To do that a data class that represents the relationship between the questions-options and questions-user_answers should be created. Doing that, we end up with a model like this:

data class QuestionOptionsUserAnswer(
    @Embedded
    val question: QuestionLocal,

    @Relation(
        parentColumn = "id",
        entityColumn = "question_id"
    )
    val options: List<OptionLocal>,

    @Relation(
        parentColumn = "id",
        entityColumn = "question_id"
    )
    val userAnswer: UserAnswerLocal? = null
)

Since the query is done on the questions table, the question object is annotated with
@Embedded. We annotate the other objects with @Relation. This tells Room to return the relation objects along with the question object. In this case, the relation between the embedded and other related entities is defined by the parentColumn and the entityColumn. The parentColumn refers to the column in the embedded entity that is used to perform the match/join operation with the relation entity, while the entityColumn refers to the column in the relation entity that is used to perform the match/join operation with the embedded entity. What this means is to get the options that belong to a particular question, the id column in the questions table has to match the question_id column in the options table. To then get the questions with their options and possible user-selected answer the query would look like this:

@Transaction
@Query("select * from questions")
suspend fun getQuestions(): List<QuestionOptionsUserAnswer>