How can I filter composite ManyToMany POJOs by children POJO’s attributes?

I have two Room Entities like this ones:

@Entity
public class Teacher implements Serializable {
    @PrimaryKey(autoGenerate = true)
    public int id;

    @ColumnInfo(name = "name")
    public String name;
}

@Entity
public class Course implements Serializable {
    @PrimaryKey(autoGenerate = true)
    public short id;

    @ColumnInfo(name = "name")
    public String name;
}

…and a junction table for Many To Many relationship like this one:

@Entity(primaryKeys = {"teacher_id", "course_id"})
public class TeachersCourses implements Serializable {
    @ColumnInfo(name = "teacher_id")
    public int teacherId;

    @ColumnInfo(name = "course_id")
    public short courseId;

    @ColumnInfo(index = true, name = "course_order")
    public short courseOrder;
}

…and some composite class for obtaining some kind of “composite POJO”:

public class TeacherWithCourses implements Serializable {
    @Embedded public Teacher teacher;
    @Relation(
            parentColumn = "id",
            entity = Course.class,
            entityColumn = "id",
            associateBy = @Junction(
                    value = TeachersCourses.class,
                    parentColumn = "teacher_id",
                    entityColumn = "course_id"
            )
    )
    public List<Courses> courses;
}

…then, I have this kind of “composite DAO”:

@Dao
public abstract class TeacherWithCoursesDao {
    [...]

    // XXX This one works as expected
    @Transaction
    @Query("SELECT * FROM teacher " +
           "WHERE id=:teacher_id"
    )
    public abstract LiveData<List<TeacherWithCourses>> getTeachersByTeacherId(int teacher_id);

    // XXX FIXME
    // This one succeeds at loading "parents", but each "parent"'s list of "children" is empty
    @Transaction
    @Query("SELECT * FROM teacher " +
            "INNER JOIN teacherscourses AS tc ON teacher.id = tc.teacher_id " +
            "INNER JOIN course AS c ON c.id = tc.course_id " +
            "WHERE tc.course_id = :course_id " +
            "ORDER BY teacher.id ASC, tc.course_order ASC"
    )
    public abstract LiveData<List<TeacherWithCourses>> getTeachersByCourseId(short course_id);
}

The point of the question is…

The one that works returns the list as expected: with each TeacherWithCourses having the Teacher and the List of Courses. The second one does not: the resulting TeacherWithCourses objects have it’s Teacher properties loaded correctly, but the List<Courses> property has an empty list, although the complex SELECT query based on INNER JOINS filters as expected.

So, how can I obtain a list of complete TeacherWithCourses objects like in the first DAO method, but filtering by Courses ID instead?

Answer

I believe that your issue is due to the column names being duplicated and basically room selecting the incorrect values (I believe it uses the last so it would be using the course id column value for the teacher id).

That is the query (with the JOINS) will consist of columns:-

• id (Teacher),
• name (Teacher),
• teacher_id,
• course_id,
• id (Course),
• name (Course)

So let’s say you have the following in the database :-

And the following is used (LiveData not used to brevity and convenience) :-

    for(Course c: dao.getAllCourses()) {
        for (TeacherWithCourses tbc: dao.getTeachersByCourseId(c.id)) {
            Log.d("TEACHER","Teacher is " + tbc.teacher.name + " Courses = " + tbc.courses.size());
            for(Course course: tbc.courses) {
                Log.d("COURSE","tCourse is " + course.name);
            }
        }
    }

Then the result, is as you report :-

2021-11-10 15:25:30.994 D/TEACHER: Teacher is Course1 Courses = 0
2021-11-10 15:25:30.996 D/TEACHER: Teacher is Course2 Courses = 0
2021-11-10 15:25:30.999 D/TEACHER: Teacher is Course3 Courses = 0
2021-11-10 15:25:30.999 D/TEACHER: Teacher is Course3 Courses = 0

However (Fix)

If you use different column names e.g. :-

@Entity
public class AltCourse implements Serializable {
    @PrimaryKey(autoGenerate = true)
    public short courseid; //<<<<<<<<<<

    @ColumnInfo(name = "coursename") //<<<<<<<<<<
    public String coursename; //<<<<<<<<<< doesn't matter

}

• and data is added that basically duplicates the original courses (same id #’s) so :-

• 

Along with :-

public class AltTeacherWithCourses implements Serializable {
    @Embedded
    public Teacher teacher;
    @Relation(
            parentColumn = "id",
            entity = AltCourse.class, //<<<<<<<<<< just to use alternative class
            entityColumn = "courseid", //<<<<<<<<<<
            associateBy = @Junction(
                    value = TeachersCourses.class,
                    parentColumn = "teacher_id",
                    entityColumn = "course_id"
            )
    )
    public List<AltCourse> courses; //<<<<<<<<<< just to use alternative class
}

• noting that the teachercourses table is used just that the alternative Courses are linked (rather than create an altteachercourses table)

and :-

@Transaction
@Query("SELECT * FROM teacher " +
        "INNER JOIN teacherscourses AS tc ON teacher.id = tc.teacher_id " +
        "INNER JOIN altcourse AS c ON c.courseid = tc.course_id " +
        "WHERE tc.course_id = :course_id " +
        "ORDER BY teacher.id ASC, tc.course_order ASC"
)
public abstract List<AltTeacherWithCourses> getAltTeachersByCourseId(short course_id);

and then :-

    for(Course c: dao.getAllCourses()) {
        for (AltTeacherWithCourses tbc: dao.getAltTeachersByCourseId(c.id)) {
            Log.d("TEACHER","Teacher is " + tbc.teacher.name + " Courses = " + tbc.courses.size());
            for(AltCourse course: tbc.courses) {
                Log.d("COURSE","tCourse is " + course.coursename);
            }
        }
    }

i.e. instead of Course, AltCourse is used in an otherwise an identical, then the result is :-

2021-11-10 15:41:09.223 D/TEACHER: Teacher is Teacher1 Courses = 3
2021-11-10 15:41:09.223 D/COURSE:   Course is AltCourse1
2021-11-10 15:41:09.223 D/COURSE:   Course is AltCourse2
2021-11-10 15:41:09.223 D/COURSE:   Course is AltCourse3
2021-11-10 15:41:09.225 D/TEACHER: Teacher is Teacher1 Courses = 3
2021-11-10 15:41:09.225 D/COURSE:   Course is AltCourse1
2021-11-10 15:41:09.225 D/COURSE:   Course is AltCourse2
2021-11-10 15:41:09.225 D/COURSE:   Course is AltCourse3
2021-11-10 15:41:09.229 D/TEACHER: Teacher is Teacher1 Courses = 3
2021-11-10 15:41:09.229 D/COURSE:   Course is AltCourse1
2021-11-10 15:41:09.229 D/COURSE:   Course is AltCourse2
2021-11-10 15:41:09.229 D/COURSE:   Course is AltCourse3
2021-11-10 15:41:09.230 D/TEACHER: Teacher is Teacher2 Courses = 1
2021-11-10 15:41:09.230 D/COURSE:   Course is AltCourse3

As such the solution is to either

1. use unique column names, or
2. use the @Prefix annotation (parameter of the @Embedded) e.g. you could have

:-

public class TeacherWithCourses implements Serializable {
    @Embedded(prefix = "prefix_teacher_") //<<<<<<<<<<
    public Teacher teacher;
    @Relation(
            parentColumn = "prefix_teacher_id", //<<<<<<<<<<
            entity = Course.class,
            entityColumn = "id",
            associateBy = @Junction(
                    value = TeachersCourses.class,
                    parentColumn = "teacher_id",
                    entityColumn = "course_id"
            )
    )
    public List<Course> courses;
}

and use :-

@Transaction
@Query("SELECT teacher.id AS prefix_teacher_id, teacher.name AS prefix_teacher_name, c.* FROM teacher " +
        "INNER JOIN teacherscourses AS tc ON teacher.id = tc.teacher_id " +
        "INNER JOIN course AS c ON c.id = tc.course_id " +
        "WHERE tc.course_id = :course_id " +
        "ORDER BY teacher.id ASC, tc.course_order ASC"
)
public abstract List<TeacherWithCourses> getTeachersByCourseId(short course_id);

BUT you would then also need to use :-

@Transaction
@Query("SELECT id AS prefix_teacher_id, name as prefix_teacher_name FROM teacher " +
        "WHERE id=:teacher_id"
)
public abstract List<TeacherWithCourses> getTeachersByTeacherId(int teacher_id);

Additional re comment :-

the only issue is that “ORDER BY” statement didn’t seem to affect this “child list” ‘s sorting. But that one might be subject for a new question.

The issue is due to how @Relationship works.

@Relationship works by getting ALL the @Relation objects of the parent(s) via underlying queries. Anything in the @Query that doesn’t affect the parents that are retrieved is not considered when retrieving the children. Hence, you have no control over the ORDER.

Perhaps consider a CourseWithTeachers approach, but then you have no control over the ORDER of the Teachers. The alternative is to use @Embedded for the parent and the child but you then have to process the result which is the caretesian product i.e. a result for each parent/child combination.