Blog Infos
Author
Published
Topics
,
Author
Published
APIs considered
Multi-table query
Multi-map
@Relation annotated data classes
Sample app
Use case 1 — Query answers for free text questions
@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 🎊

Use Case 2 — Query answers for multiple choice questions (MCQ)
Use Case 2.1 — Query questions & options only
@Query("SELECT * from question LEFT JOIN option ON question_id")
fun readMcqs(): Flow<Map<QuestionEntity, List<OptionEntity>>>

@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!

 

@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();
    }
  });
}
Use Case 2.2 — Query 3 objects Question, Option and Answer
@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")

Finally, I’ve turned to the classic @Relation annotation method and it met our requirements here:

// the Entity
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>>

 

Database design matters
What about multiple separate SQL queries?
Conclusion
Source code
Credits

This article was originally published on proandroiddev.com

Job Offers

Job Offers

There are currently no vacancies.

OUR VIDEO RECOMMENDATION

,

REST in Peace: A Journey Through API Protection

Isn’t Droidcon a Mobile Developer’s conference? So, why would I care about protecting REST APIs? Well, think twice! API protection starts in the app, as the protection level of the API will be only as…
Watch Video

REST in Peace: A Journey Through API Protection

Andreas Luca & Marc Obrador Sureda
Head of Solution Integration & CTO , Co-founder
Build38

REST in Peace: A Journey Through API Protection

Andreas Luca & Mar ...
Head of Solution Int ...
Build38

REST in Peace: A Journey Through API Protection

Andreas Luca & M ...
Head of Solution Integrat ...
Build38

Jobs

YOU MAY BE INTERESTED IN

YOU MAY BE INTERESTED IN

blog
This tutorial is the second part of the series. It’ll be focussed on developing…
READ MORE
blog
We recently faced a problem with our application getting updated and reaching slowly to…
READ MORE
blog
A few weeks ago I started with a simple question — how to work…
READ MORE
blog
One of the main functions of a mobile phone was to store contacts information.…
READ MORE

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu