ManyToMany POJOS in RoomDB: how can I obtain related objects's list sorted by junction sorting number?

Question

In the same code that I used in this question, I would like to obtain the "composite ManyToMany POJO"s with it's "related objects list" sorted by a number column in the junction table.

My DAO's SQL STATEMENT

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

The generated DAO's method obtains the list of TeacherWithCourses objects as expected. These objects' courses list property gets the related objects as expected; but the "ORDER BY" clause doesn't seem to affect how these courses list is sorted at all.

I expected each internal list in these objects (List<Course> courses) to be sorted according to the junction table's tc.course_order number; but the obtained objects seem to come sorted arbitrarily.

The "composite" POJO

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

---

Is this kind of sorting possible through SQL query, or must I implement it some other way?

[EDIT] The Junction Entity

I store the ordering criteria inside an extra column here:

@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;
}

Answer 1

but the obtained objects seem to come sorted arbitrarily.

This is because of how @Relation works. It does not include/consider the child objects from the query, it uses the query just to extract and build the parent. It then gets, irrespective of the supplied query, ALL of the children for the parent via a query that is build according to the attributes of the @Relation.

As such to filter and or order the children you need to supplement @Relation by overwriting the list/array of children.

For convenience based upon my previous answer (so using AltCourse with changed column names) here's an example of one way that is easy to implement (but not the most efficient).

First the additional @Dao components :-

@Query("SELECT altcourse.* FROM altcourse " +
        "JOIN teacherscourses ON teacherscourses.course_id = altcourse.courseid " +
        "JOIN teacher ON teacher.id = teacherscourses.teacher_id " +
        "WHERE teacher.id=:id ORDER BY teacherscourses.course_order ASC")
public abstract List<AltCourse> getSortedCoursesForATeacher(int id);


@Transaction
public List<AltTeacherWithCourses> getAltTeachersByCourseIdSorted(short course_id) {
    List<AltTeacherWithCourses> rv = getAltTeachersByCourseId(course_id);
    for (AltTeacherWithCourses twc: rv) {
        twc.courses = getSortedCoursesForATeacher(twc.teacher.id);
    }
    return rv;
}

• obviously change anything starting with Alt accordingly

So a query that extracts the courses in the correct order, and

A method that gets the original teachersWithCourses where the Courses are not sorted as expected and replaces the list of courses with the sorted courses as extracted by the additional query.

Alt version have been used in the above for testing so with the tables looking like :-

and

Running :-

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

Results in :-

2021-11-11 15:26:01.559 D/TEACHER_SORTC: Teacher is Teacher1 Courses = 3
2021-11-11 15:26:01.559 D/COURSE_SORTC:     Course is AltCourse3
2021-11-11 15:26:01.559 D/COURSE_SORTC:     Course is AltCourse2
2021-11-11 15:26:01.559 D/COURSE_SORTC:     Course is AltCourse1
2021-11-11 15:26:01.565 D/TEACHER_SORTC: Teacher is Teacher1 Courses = 3
2021-11-11 15:26:01.565 D/COURSE_SORTC:     Course is AltCourse3
2021-11-11 15:26:01.565 D/COURSE_SORTC:     Course is AltCourse2
2021-11-11 15:26:01.565 D/COURSE_SORTC:     Course is AltCourse1
2021-11-11 15:26:01.568 D/TEACHER_SORTC: Teacher is Teacher1 Courses = 3
2021-11-11 15:26:01.568 D/COURSE_SORTC:     Course is AltCourse3
2021-11-11 15:26:01.569 D/COURSE_SORTC:     Course is AltCourse2
2021-11-11 15:26:01.569 D/COURSE_SORTC:     Course is AltCourse1
2021-11-11 15:26:01.569 D/TEACHER_SORTC: Teacher is Teacher2 Courses = 1
2021-11-11 15:26:01.569 D/COURSE_SORTC:     Course is AltCourse3

Additional

An alternative/more efficient (SQLite wise) approach is to use a more complex query along with a POJO that uses @Embedded for Teacher and Course.

Thus you extract objects that have a Teacher and a Course and then you can build the TeachersWithCourses from the extract.

This does not need additional queries as are run when using @Relation and thus there is no need for an @Transaction.

• note the example is using the original Teacher and Course without unique names so would have to be modified accordingly.

First the TeacherCourse POJO :-

class TeacherCourse {
    @Embedded
    Teacher teacher;
    @Embedded(prefix = "course")
    Course course;
} 

• prefix used to circumvent duplicate column names.

The query :-

@Query("WITH teacher_in_course AS (" +
        "SELECT teacher.id " +
        "FROM teacher " +
        "JOIN teacherscourses ON teacher.id = teacherscourses.teacher_id " +
        "WHERE course_id = :courseId" +
        ")" +
        "SELECT course.id AS courseid, course.name as coursename, teacher.* " +
        "FROM course " +
        "JOIN teacherscourses ON course.id = teacherscourses.course_id " +
        "JOIN teacher ON teacher.id = teacherscourses.teacher_id " +
        "WHERE teacher.id IN (SELECT * FROM teacher_in_course) " +
        "ORDER BY teacher.id ASC, course_order ASC" +
        ";")
public abstract  List<TeacherCourse> getTeachersCoursesSortedFromCourseId(short courseId);

• the CTE (Common Table Expression) teacher_in_course retrieves the list of teachers who have the specified courseid. This is used in the actual query to get the teachers and ALL courses for each of the teachers ordered accordingly. As such there is no need for @Transaction as all the data is extracted in the single query.

However, a TeachersWithCourses list needs to be built from the list of TeacherCourse objects e.g. :-

public List<TeacherWithCourses> buildTeacherWithCoursesListFromTeacherCourseList(List<TeacherCourse> teacherCourseList) {

    ArrayList<TeacherWithCourses> rv = new ArrayList<>();
    boolean afterFirst = false;
    TeacherWithCourses currentTWC = new TeacherWithCourses();
    currentTWC.teacher = new Teacher(-1,"");
    ArrayList<Course> currentCourseList = new ArrayList<>();
    currentTWC.courses = currentCourseList;
    for (TeacherCourse tc: teacherCourseList) {
        if(currentTWC.teacher.id != tc.teacher.id) {
            if (afterFirst) {
                currentTWC.courses = currentCourseList;
                rv.add(currentTWC);
                currentCourseList = new ArrayList<>();
                currentTWC = new TeacherWithCourses();
            }
            currentTWC.teacher = tc.teacher;
            currentTWC.courses = new ArrayList<>();
        }
        currentCourseList.add(tc.course);
        afterFirst = true;
    }
    if (afterFirst) {
        currentTWC.courses = currentCourseList;
        rv.add(currentTWC);
    }
    return rv;
}

Answer 2

From what it looks like you have two options. Here is an article explaining both methods https://newbedev.com/using-room-s-relation-with-order-by

You can sort your List after its been pulled from the database. Using Collections.Sort. This will remove a lot of complexity from your query. And is probably the best option.

or

You can split your query into 3 parts and manually create a List of TeacherWithCourses, a query pulls a List of Teacher and then uses each teacher to pull their Courses in the order you desire. This is more queries but can be down under a single @Transaction so there will be little felt repercussions on the database side. But be careful this can create a lot of objects fast.