Take this example: I have a form that has several sections, each of which has questions. Laterally, I have answers that map to questions, and they have another column that I want to filter on request:
So, I have the following objects:
@Entity(tableName = "sections") public class Section { @PrimaryKey public long id; public String title; } @Entity(tableName = "questions") public class Question { @PrimaryKey public long id; public String title; public long sectionId; } @Entity(tableName = "answers") public class Answer { @PrimaryKey public long id; public long questionId; public int otherColumn; }
In the DAO section, I want to get all of them.
Here is the POJO that I want to fill out on this request:
class SectionWithQuestions { @Embedded public Section section; @Relation(parentColumn = "id", entityColumn = "sectionId", entity = Question.class) public List<QuestionWithAnswer> questions; public static class QuestionWithAnswer { @Embedded public Question question; @Relation(parentColumn = "id", entityColumn = "questionId", entity = Answer.class) List<Answer> answers; } }
In another application, the query will look like this:
SELECT s.*, q.*, a.* FROM sections s LEFT JOIN questions q ON q.sectionId = s.id LEFT JOIN answers a ON a.questionId = q.id WHERE s.id = :sectionId and a.otherColumn = :otherColumn
However, in the Room, I found that if you want an object and their relationship (for example, the user and his pets in the example), you select only the object, and the relationship is requested in the second request. It will be:
@Query("SELECT * FROM sections WHERE id = :sectionId")
Then in the generated code will be (pseudocode):
sql = "SELECT * FROM sections WHERE id = :sectionId" // what inside @Query cursor = query(sql) int indexColumn1 = cursor.getColumnIndex(col1) int indexColumn2 ... etc while (cursor.moveToNext) { masterObject = new object() masterObject.property1 = cursor.get(indexColumn1) ... etc __fetchRelationshipXXXAsYYY(masterObject.relations) // fetch the child objects }
and this __fetch XXX as YYY method looks like this:
sql = "SELECT field1, field2, ... FROM a WHERE foreignId IN (...)" similar algo as previously: fetch column indices, and loop through the cursor
So basically it creates 2 queries: one for the main object and one for the relationship. The second request is created automatically, and we do not control it.
To get back to my problem where I need relationships, but also filter by child column, I got stuck:
- in the first query, I cannot reference the
otherColumn column because it does not exist - in
@Relation I can neither because the only properties of this annotation are the join column and entity definition
Is this possible in the Room or should I make sub-queries myself?
Bonus question: why not join the tables in one query, and instead create 2 queries? Is it for performance reasons?
Edit to clarify what I expected:
What I expected to write:
@Query("SELECT s.*, q.*, a.* " + "FROM sections s " + "LEFT JOIN questions q ON q.sectionId = s.id " + "LEFT JOIN answers a ON a.questionId = q.id " + "WHERE s.id = :sectionId and a.otherColumn = :additionalIntegerFilter") SectionWithQuestionsAndAnswers fetchFullSectionData(long sectionId); static class SectionWithQuestionsAndAnswers { @Embedded Section section; @Relation(parentColumn = "id", entityColumn = "sectionId", entity = Question.class) List<QuestionWithAnswers> questions; } static class QuestionWithAnswers { @Embedded Question question; @Relation(parentColumn = "id", entityColumn = "questionId", entity = Answer.class) Answer answer;
This pseudo-code, which, as I thought, would be implemented by Room as the generated code:
function fetchFullSectionData(long sectionId, long additionalIntegerFilter) { query = prepare(sql); // from @Query query.bindLong("sectionId", sectionId); query.bindLong("additionalIntegerFilter", additionalIntegerFilter); cursor = query.execute(); Section section = null; long prevQuestionId = 0; Question question = null; while (cursor.hasNext()) { if (section == null) { section = new Section(); section.questions = new ArrayList<>(); section.field1 = cursor.get(col1); // etc for all fields } if (prevQuestionId != cursor.get(questionIdColId)) { if (question != null) { section.questions.add(question); } question = new Question(); question.fiedl1 = cursor.get(col1); // etc for all fields prevQuestionId = question.id; } if (cursor.get(answerIdColId) != null) { // has answer Answer answer = new Answer(); answer.field1 = cursor.get(col1); // etc for all fields question.answer = answer; } } if (section !=null && question != null) { section.questions.add(question); } return section; }
This request and all my objects are retrieved.