我如何通过儿童POJO的属性来合成ManyToMany POJO？ [英] How can I filter composite ManyToMany POJOs by children POJO's attributes?

问题描述

我有两个类似的Room实体：

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

.和多对多关系的连接表，如下所示：

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

.和一些用于获取某种类型的复合POJO&QOOT；的复合类：

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

.那么，我有这种复合刀(&Q；)：

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

问题的重点是.

工作的那个会按预期返回列表：每个TeacherWithCourses都有老师和List课程。第二个并非如此：生成的TeacherWithCourses对象正确加载了Teacher属性，但是List<Courses>属性有一个空列表，尽管基于INNER JOINS的复杂SELECT查询按预期筛选。

那么，如何像第一个DAO方法一样获取完整的TeacherWithCourses对象列表，但改为按课程ID进行筛选？

推荐答案

我认为您的问题是由于列名重复，并且基本上房间选择了不正确的值(我认为它使用最后一个值，因此将使用课程ID列值作为教师ID)。

即查询(带有联接)将由列组成：-

• id(教师)，
• 姓名(教师)，
• 教师id，
• Course_id，
• id(课程)，
• 名称(课程)

假设您的数据库中有以下内容：-

并且使用了(LiveData不习惯简洁方便)：-

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

然后，结果如您所报告的那样：-

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

但是(修复)

如果使用不同的列名，例如：-

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

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

}

• 添加的数据基本上复制了原始课程(相同的ID#)，因此：-

与：-

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
}

• 请注意，使用教师课程表只是说明链接了替代课程(而不是创建altTeacherCourses表)

和：-

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

然后：-

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

即，当然不使用AltCourse，而是在其他相同的中使用AltCourse，则结果为：-

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

因此，解决方案是

1. 使用唯一列名，或
2. 使用@Prefix注释(@Embedded的参数)，例如您可以使用

：-

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

并使用：-

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

但是您还需要使用：-

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

其他评论：-

唯一的问题是"；ORDER BY&QOOT；语句似乎不会影响此&QOOT；子列表&QOOT；的排序。但该子列表可能会出现新问题。

该问题是由于@Relationship的工作方式造成的。

@Relationship通过基础查询获取父级的所有@Relation对象。在检索子查询时，不会考虑@查询中任何不影响所检索的父级的内容。因此，您无法控制订单。

也许可以考虑"与教师一起上课"的方法，但这样您就无法控制教师的顺序了。另一种方法是对父项和子项使用@Embedded，但随后必须处理结果，即每个父项/子项组合的结果。

这篇关于我如何通过儿童POJO的属性来合成ManyToMany POJO？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！