RoomDB中的ManyToMany POJOS：如何获取按连接排序编号排序的相关对象列表？ [英] ManyToMany POJOS in RoomDB: how can I obtain related objects's list sorted by junction sorting number?

问题描述

在与我在this question中使用的代码相同的代码中，我希望获得"；复合ManyToMany Pojo"；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列表属性按预期获取相关对象；但是"；order by"；子句似乎根本不影响这些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：如何获取按连接排序编号排序的相关对象列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！