RoomDB中的ManyToMany POJOS：如何获取按连接排序编号排序的相关对象列表？
在与我在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;
}
相关文章