本文介紹了RoomDB中的ManyToMany POJOS：如何獲取按連接排序編號排序的相關對象列表？的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

在與我在this question中使用的代碼相同的代碼中，我希望獲得&quot；復合ManyToMany Pojo&quot；s及其相關對象列表(&Quot；Related Objects List&Quot；)，并按連接表中的數字列排序。

我的DAO的SQL語句

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

生成的DAO方法如期獲得TeacherWithCourses對象列表。這些對象的courses列表屬性按預期獲取相關對象；但是&quot；order by&quot；子句似乎根本不影響這些courses列表的排序方式。

我預計這些對象(List<Course> courses)中的每個內部列表都會根據連接表的tc.course_order編號進行排序；但獲得的對象似乎是任意排序的。

復合POJO

(&Q；)

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

這種排序可以通過SQL查詢實現嗎？還是必須以其他方式實現？

[編輯]交匯點實體

我將訂購條件存儲在此處的一個額外列中：

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

推薦答案

但獲取的對象似乎是任意排序的。

這是因為@Relation的工作方式。它不包括/考慮來自查詢的子對象，它只使用查詢來提取和構建父對象。然后，不管提供的查詢如何，它都會通過根據@Relation的屬性構建的查詢獲取父級的所有子級。

因此，要過濾和/或通過覆蓋子項列表/數組來訂購需要補充@Relationship的子項。

為方便起見，根據我之前的回答(因此使用AltCourse更改的列名)下面是一個易于實現(但不是最高效)的方法示例。

首先添加@DAO組件：-

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

顯然要相應地更改以Alt開頭的所有內容

以正確順序提取課程的查詢，

獲取原始teachersWithCourses(其中課程未按預期排序)并將課程列表替換為附加查詢提取的已排序課程的方法。

Alt以上版本已用于測試，因此表格如下：-

和

運行：-

    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","	Course is " + course.coursename);
            }
        }
    }

結果如下：-

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

其他

另一種/更有效的方法(SQLite明智)是使用更復雜的查詢以及使用@Embedded表示教師和課程的POJO。

這樣，您提取了具有教師和課程的對象，然后可以從提取中構建TeachersWithCourses。

這不需要使用@Relation時運行的其他查詢，因此不需要@Transaction。

注意：該示例使用的是沒有唯一名稱的原始教師和課程，因此必須進行相應修改。

首先是教師課程POJO：-

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

用于避免重復列名的前綴。

查詢：-

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

CTE(公用表表達式)teacher_in_course檢索具有指定課程ID的教師列表。這在實際查詢中用于獲取教師以及每個教師相應訂購的所有課程。因此，不需要@Transaction，因為所有數據都是在單個查詢中提取的。

但是，需要從TeacherCourse對象列表構建一個TeachersWithCourses列表，例如：-

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

這篇關于RoomDB中的ManyToMany POJOS：如何獲取按連接排序編號排序的相關對象列表？的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，