Room Database — Lessons learnt from working with multiple-table joins | by Eric N | Jan, 2023 | ProAndroidDev

In this article, I'm going to examine three methods of querying multiple tables at once, namely multi-table query, multi-map and @Relation-annotated data class and their pros & cons through a Q&A sample app. I'll start by showing how to query answers for free text questions and then moves on to a more challenging use case: multiple-choice answers

APIs considered

As of Room version 2.4.3, there are 3 methods to query multiple database tables at once

Multi-table query

Pros

• Been available since version 1.1
• Reliable, less complex join logic/ magic under the hood
• Straightforward

Cons

• Extra data class required
• The extra data class can be large if we need a lot of columns

Multi-map

Pros

• No need for extra classes
• Seemingly intuitive

Cons

• Relatively new, since Room 2.4.0
• Quite a bit of join logic/ magic under the hood
• Doesn't work for 3 tables or more - We'll explore such limitations in our sample app!

@Relation annotated data classes

Pros

• Extra data classes required
• Each class is small even if we need a lot of columns (as opposed to Multi-table query)

Cons

• Been available since 1.0
• Reliable
• Works for 3 tables or more
• Works for nested relationships - caution should be exercised in terms of performance though
• Quite a bit of join logic/ magic under the hood

Sample app

What better way to learn by doing? For the purpose of learning the nuances of multi-table Room queries, we'll use a simple Question and Answers (Q&A) app.
Our Q&A app supports both text-based and choice-based questions.
An example of a text-based question is "What is your favorite food?". An example of a multiple-choice question is "Relationships (priorities?)" to which the user can answer "Communication", "Compassion", "Collaboration" and/ or "Commitment".
The data structures and their relationships are as follows:

We'll start with the simplest use case which is reading text answers from our Room database and then move on to a more challenging use case: multiple-choice answers.
Source code is at https://github.com/ericntd/myqa

Use case 1 - Query answers for free text questions

Multi-table query is a good fit here because of its simplicity and the small number of columns we require

@Query("SELECT question.text as question, answer.text_value as answer FROM question, answer WHERE question.id = answer.question_id AND answer.option_id = ''")
fun readTextAnswers(): Flow<List<TextAnswer>>

With the extra class TextAnswer

data class TextAnswer(
val question: String,
val answer: String?
)

The results 🎊

Source code: https://github.com/ericntd/myqa/tree/text-based-questions
We can of course achieve the same results with multi-map and @Relation but those approaches would be overly complicated for our simple use case.

Use Case 2 - Query answers for multiple choice questions (MCQ)

Use Case 2.1 - Query questions & options only

Let's start by showing what options the user can choose for each question.
And let's start with the newer and seemingly more intuitive method which is Multi-map.

@Query("SELECT * from question LEFT JOIN option ON question_id")
fun readMcqs(): Flow<Map<QuestionEntity, List<OptionEntity>>>

No extra class needed
The results

Wow, everything seems messed up. Take note that the text in bold is supposed to be the questions such as Relationships but instead, they appear to be the options available for those questions.
This is because Room Multi-Map cannot support tables with the same column names at the moment. In our case, both the Question and Option tables have the same primary key "id". The solution is to make the column names unique!
Once we change our data classes to

@Entity(tableName = "question")
data class QuestionEntity(
@PrimaryKey @ColumnInfo(name = "question_id") val id: String,
@ColumnInfo(name = "question_text") val text: String
)

And

@Entity(tableName = "option")
data class OptionEntity(
@PrimaryKey @ColumnInfo(name = "option_id") val id: String,
@ColumnInfo(name = "question_id") val questionId: String,
@ColumnInfo(name = "option_text") val text: String,
val humanId: String?
)

Now it works!

Source code: https://github.com/ericntd/myqa/tree/multiple-choice-questions-multi-map-fixed-duplicate-columns
Under the hood, Room compiler generates good old Cursor code that extracts the Question and Option objects from the results of our SQL query:

@Override
public Flow<Map<QuestionEntity, List<OptionEntity>>> readMcqs() {
  final String _sql = "SELECT * from question INNER JOIN option ON option.question_id = question.question_id";
  final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 0);
  return CoroutinesRoom.createFlow(__db, false, new String[]{"question","option"}, new Callable<Map<QuestionEntity, List<OptionEntity>>>() {
    @Override
    public Map<QuestionEntity, List<OptionEntity>> call() throws Exception {
      final Cursor _cursor = DBUtil.query(__db, _statement, false, null);
      try {
        final int _cursorIndexOfId = CursorUtil.getColumnIndexOrThrow(_cursor, "question_id");
        // other columns…
        final Map<QuestionEntity, List<OptionEntity>> _result = new LinkedHashMap<QuestionEntity, List<OptionEntity>>();
        while (_cursor.moveToNext()) {
        // extra Question and Option and put into _result…
        }
        return _result;
      } finally {
      _cursor.close();
      }
    }
    @Override
    protected void finalize() {
      _statement.release();
    }
  });
}

I've noticed there are some weird unused cursor indexes such as final int _cursorIndexOfId_1 = CursorUtil.getColumnIndexOrThrow(_cursor, "question_id") but I'm guessing this is probably just a readability problem rather than a functional problem with Room's generated code.

Use Case 2.2 - Query 3 objects Question, Option and Answer

Multi-map doesn't seem to support this kind of usage at the moment, unfortunately. I hope future versions of Multi-map would support querying 3 related objects.
I've tried the following:

@Query("SELECT * FROM (SELECT * from question INNER JOIN option ON option.question_id = question.question_id) AS q INNER JOIN answer ON answer.question_id = q.question_id")
fun readMcqAnswers2(): Flow<Map<Map<QuestionEntity, List<OptionEntity>>, List<AnswerEntity>>>

And Room was unable to generate code to meet the requirements:

error: Not sure how to convert a Cursor to this method's return type (kotlinx.coroutines.flow.Flow<java.util.Map<java.util.Map<app.ericn.myqa.QuestionEntity, java.util.List<app.ericn.myqa.OptionEntity>>, java.util.List<app.ericn.myqa.AnswerEntity>>>).
public abstract kotlinx.coroutines.flow.Flow<java.util.Map<java.util.Map<app.ericn.myqa.QuestionEntity, java.util.List<app.ericn.myqa.OptionEntity>>, java.util.List<app.ericn.myqa.AnswerEntity>>> readMcqAnswers2();

Similarly, Room doesn't know how to generate code for the following either:

@Query("SELECT * FROM question")
fun readMcqAnswers5(): Flow<Map<Map<QuestionEntity, List<OptionEntity>>, List<AnswerEntity>>>
@Query("SELECT * FROM question INNER JOIN (SELECT * FROM option INNER JOIN answer ON answer.option_id = option.option_id) as a ON a.question_id = question.question_id")
fun readMcqAnswers3(): Flow<Map<QuestionEntity, Map<OptionEntity, AnswerEntity>>>
@Query("SELECT * FROM question")
fun readMcqAnswers4(): Flow<Map<QuestionEntity, Map<OptionEntity, AnswerEntity>>>

Finally, I've turned to the classic @Relation annotation method and it met our requirements here:
The data model

data class QuestionWithRelations(
    @Embedded
    val question: QuestionEntity,
    @Relation(
        parentColumn = "question_id",
        entityColumn = "question_id"
    )
    val options: List<OptionEntity>,
    @Relation(
        parentColumn = "question_id",
        entityColumn = "question_id"
    )
    val answers: List<AnswerEntity>
)

The DAO

@Query("SELECT * FROM question")
fun readMcqAnswers1(): Flow<List<QuestionWithRelations>>

Source code: https://github.com/ericntd/myqa/tree/multiple-choice-questions-answers
Under the hood, Room also generates code that leverages good old SQLite Cursor but it seems more readable to me compared to the Multi-map's code. I don't see a reason why future iterations of Multi-map can't match the functionalities of @Relation and @Embedded for 3 objects. It's just unclear how common such a requirement is and where it falls in the Room development team's priority chart.

Database design matters

If each answer includes a list of option ids, would the logic be simpler? Let me know your thoughts in a reply.
What about multiple separate SQL queries?
Multiple SQL queries are slower than a single SQL query
Concurrent SQL queries are not necessarily faster than sequential queries!

Conclusion

As always, there is no one silver bullet for all situations. You should choose the tool that best fits your needs and constraints. In our app, we currently employ all 3 different methods
Multi-table queries for simple text-based questions & answers
Multi-Map for 2 object relations e.g. Profiles and Photos
@Relation and @Embedded for 3 object relations e.g. Questions, Options and Answers

Source code

https://github.com/ericntd/myqa

Credits

Kudos to Christa Mabee for sharing the existence of Room Multi-Map and your exploratory work on it. Many thanks also for proofreading this article.