How to filter a nested relation in Room?

Let's take this example: I have a form, which has several sections, each having questions. Sideways, I have answers that are mapped to questions and they have another column that I want to filter on when querying:

So I have the following entities:

@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 section DAO I want to retrieve all of them.

Here's the POJO that I want filled by this query:

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 would be:

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 Room I have found out that if you want an object and their relations (like a user and its pets in the example), you only select the object, and the relations are queried in a second query. That would be:

@Query("SELECT * FROM sections WHERE id = :sectionId")

Then in the generated code there would be (pseudo code):

sql = "SELECT * FROM sections WHERE id = :sectionId" // what's 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 is as follows:

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 master object and one for the relations. The 2nd query is automatically created and we have no control over it.

To get back to my problem where I want relations but also filter on the child column, I'm stuck:

• in the 1st query I can't reference the otherColumn column because it doesn't exist
• in the @Relation I can't either because the only properties of this annotation are the join column and entity definition

Is this possible in Room or do I have to make the subqueries myself?

Bonus question: why don't they join tables in a single query but create 2 queries instead? Is this for performance reasons?

---

Edit to clarify what I expected:

That's 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; // I already know that @Relation expects List<> or Set<> which is
                   // not useful if I know I have zero or one relation (ensured
                   // through unique keys)
}

That's pseudo code that I imagined to 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;
}

That's one query, and all my objects fetched.

I find Room Relations hard to work with, not very flexible and much of the work is done under the hood in a way that is hard to really be sure how.

In my projects, most of the time I just create presentation objects - objects dedicated for some UI presentation that can be filled with a custom select.

That way I have much more control over what I want to fetch from DB (i.e. what I really need), and I fill that into that custom presentation object.

I'm just pasting the information provided on the feature request I posted (see my comment on my question):

> Hi there - we have recently released a new feature where relational query methods can be defined with Multimap return types. With this new feature, you should be able to achieve the results discussed in this thread. For more info on this new feature, you can check out the following resources: > > * Define relationships between objects: https://developer.android.com/training/data-storage/room/relationships > * Relational Query Methods in ADS 2021: https://youtu.be/i5coKoVy1g4?t=344 > * The new MapInfo annotation: https://developer.android.com/reference/androidx/room/MapInfo

I know link-only answers aren't great, but I didn't have the opportunity to test this. If someone has a better answer, I'll accept it.