如何在纯 SQL 查询中将多对多结果作为列表

发布于 2025-01-16 06:07:34 字数 1572 浏览 1 评论 0原文

我在两个实体上有多对多关系：Student 和 Teacher。他们的公共表是course。每个学生都可以和老师一起上一门课程。这是表模式，我用初始测试值填充它们：

 //Fill student table
 db.execSQL("INSERT OR IGNORE INTO student_table (studentId ,name , age,cityId ) VALUES  (1,'Mahdi',39 ,3)".trimIndent())

 //Fill teacher table
 db.execSQL("INSERT OR IGNORE INTO teacher_table (teacherId, name , grade ) VALUES  (1,'Zahra',99 )".trimIndent())
 db.execSQL("INSERT OR IGNORE INTO teacher_table (teacherId, name , grade ) VALUES  (2,'Shaby',120 )".trimIndent())

 //Fill course
 db.execSQL("INSERT OR IGNORE INTO course_table (courseId , studentId,teacherId  ) VALUES  (1,1,1 )".trimIndent())
 db.execSQL("INSERT OR IGNORE INTO course_table (courseId , studentId,teacherId ) VALUES  (5,1,2 )".trimIndent())
...

我想要一个结果，显示学生通过这样的课程与教师联系：

data class StudentAndTeachers(
    val student: Student,
    val teachers: List<Teacher>
)

所以我期望看到教师 1、2 与学生 1 的关系。

在 sql 中，我做了这样的连接：

SELECT * FROM student_table LEFT join course_table on  course_table.studentId = student_table.studentId LEFT JOIN teacher_table on course_table.teacherId = teacher_table.teacherId group by student_table.studentId

但是结果不会填充教师列表，并且每个学生只包含一名教师，如下所示，但在我的表中我应该看到学生（studentId = 1）

那么我如何才能使连接结果创建我的所有关系的列表？

User1 (listOf(teacher1, teacher2))

原文

I have many-to-many relation on two entities: Student and Teacher. They common table is course. Every student can have a course with a teacher. This is table schemas I'm filling them with initial test values:

 //Fill student table
 db.execSQL("INSERT OR IGNORE INTO student_table (studentId ,name , age,cityId ) VALUES  (1,'Mahdi',39 ,3)".trimIndent())

 //Fill teacher table
 db.execSQL("INSERT OR IGNORE INTO teacher_table (teacherId, name , grade ) VALUES  (1,'Zahra',99 )".trimIndent())
 db.execSQL("INSERT OR IGNORE INTO teacher_table (teacherId, name , grade ) VALUES  (2,'Shaby',120 )".trimIndent())

 //Fill course
 db.execSQL("INSERT OR IGNORE INTO course_table (courseId , studentId,teacherId  ) VALUES  (1,1,1 )".trimIndent())
 db.execSQL("INSERT OR IGNORE INTO course_table (courseId , studentId,teacherId ) VALUES  (5,1,2 )".trimIndent())
...

I wan to have a result that show students that connected to teachers by courses like this:

data class StudentAndTeachers(
    val student: Student,
    val teachers: List<Teacher>
)

So I expected to see teacher 1, 2 in relation with student 1.

In sql I did a join like this:

SELECT * FROM student_table LEFT join course_table on  course_table.studentId = student_table.studentId LEFT JOIN teacher_table on course_table.teacherId = teacher_table.teacherId group by student_table.studentId

But result wont fill list of teachers and only contain one teacher per student like bellow, but in my table I should see two teachers ( teacher id 1,2 ) for student ( studentId = 1 )

So how I can make result of join create list of my all relation ?

User1 (listOf(teacher1, teacher2))

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一刻暧昧 2025-01-23 06:07:34

很难想象这个模式是什么。但看起来您没有多对多关系，因为您可以通过诸如映射表、参考表、参考表等术语来完成此操作关联表 ....（基本上都是同一件事）

这样的表通常有两列，一列引用/映射其中一个表（例如学生），另一列引用/映射到另一个表，例如课程。

无论如何，这是您似乎想要实现的目标的一个示例。它有一张学生表、一张教师表和一张课程表。课程表包括一个引用/映射教师的列（假设每门课程有一名教师）。最后是映射表，它满足多对多关系，允许一门课程有许多学生，并且一个学生可以参加许多课程。

实体是：-

Teacher

@Entity(tableName = "teacher_table")
data class Teacher(
    @PrimaryKey
    var teacherId: Long? = null,
    var teacherDetails: String
)

Student

@Entity(tableName = "student_table")
data class Student(
    @PrimaryKey
    var studentId: Long? = null,
    var studentDetails: String
)

Course

@Entity(tableName = "course_table",
    /* Foreign Keys optional but enforces referential integrity */
    foreignKeys = [
        ForeignKey(
            entity = Teacher::class,
            parentColumns = ["teacherId"],
            childColumns = ["teacherIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Course(
    @PrimaryKey
    var courseId: Long? = null,
    var courseDetails: String,
    @ColumnInfo(index = true)
    var teacherIdMap: Long /* if only one teacher for the course */
)

最后映射表实体 StudentCourseMap

@Entity(tableName = "student_course_map_table",
    primaryKeys = ["studentIdMap","courseIdMap"],
    foreignKeys = [
        ForeignKey(entity = Student::class,
            parentColumns = ["studentId"],
            childColumns = ["studentIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey( entity = Course::class,
            parentColumns = ["courseId"],
            childColumns = ["courseIdMap"],
            onDelete =  ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class StudentCourseMap(
    var studentIdMap: Long,
    @ColumnInfo(index = true)
    var courseIdMap: Long
)

支持上述内容并获取课程，与教师和课程中的所有学生一起，有一个 POJO 类 CourseWithTeacherAndStudents

data class CourseWithTeacherAndStudents(

    @Embedded
    var course: Course,
    @Embedded
    var teacher: Teacher,
    @Relation(
        entity = Student::class,
        parentColumn = "courseId",
        entityColumn = "studentId",
        associateBy = Junction(
            StudentCourseMap::class,
            parentColumn = "courseIdMap",
            entityColumn = "studentIdMap"
        )
    )
    var studentList: List<Student>
)

这与您问题中的类类似（但也包括课程详细信息），并且是沿着您想要从数据库检索的内容。

data class CourseWithTeacherAndStudents(

    @Embedded
    var course: Course,
    @Embedded
    var teacher: Teacher,
    @Relation(
        entity = Student::class,
        parentColumn = "courseId",
        entityColumn = "studentId",
        associateBy = Junction(
            StudentCourseMap::class,
            parentColumn = "courseIdMap",
            entityColumn = "studentIdMap"
        )
    )
    var studentList: List<Student>
)

为了利用上面的内容，AllDAO是一个@Dao注释的抽象类（可以是一个接口，但如果你想处理来自你自己的JOINS的数据，抽象类可以有带有body的函数）。

@Dao
abstract class AllDAO {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(teacher: Teacher): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(student: Student): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(course: Course): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(courseMap: StudentCourseMap): Long
    @Query("SELECT courseId FROM course_table WHERE coursedetails=:courseDetails ")
    abstract fun getCourseIdByCourseDetails(courseDetails: String): Long
    @Transaction
    @Query("SELECT * FROM course_table JOIN teacher_table ON course_table.teacherIdMap = teacher_table.teacherId")
    abstract fun getAllCoursesWithTeacherAndStudents(): List<CourseWithTeacherAndStudents>
    
}

将它们与

一个带有相当标准的 @Database 注释类的工作示例放在一起（在本例中，为了简洁和方便，使用了 .allowMainThreadQueries）
活动中如下

：-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAO()

        val teacherId01 = dao.insert(Teacher(teacherDetails = "teacher1"))
        val teacherId02 = dao.insert(Teacher(teacherDetails = "teacher2"))
        val teacherId03 = dao.insert(Teacher(teacherDetails = "teacher3"))
        val teacherId04 = dao.insert(Teacher(teacherDetails = "teacher4")) // not used
        val courseId001 = dao.insert(Course(courseDetails = "Course1", teacherIdMap = teacherId01))
        val courseId002 = dao.insert(Course(courseDetails = "Course2", teacherIdMap = teacherId01))
        val courseId003 = dao.insert(Course(courseDetails = "Course3", teacherIdMap = teacherId02))
        val courseId004 = dao.insert(Course(courseDetails = "Course4", teacherIdMap = teacherId03))
        dao.insert(Course(courseDetails = "Course5", teacherIdMap = dao.insert(Teacher(teacherDetails = "teacher5"))))
        val studentID01 = dao.insert(Student(studentDetails = "student1"))
        val studentID02 = dao.insert(Student(studentDetails = "student2"))
        val studentID03 = dao.insert(Student(studentDetails = "student3"))
        val studentID04 = dao.insert(Student(studentDetails = "student4"))
        val studentID05 = dao.insert(Student(studentDetails = "student5"))
        val studentID06 = dao.insert(Student(studentDetails = "student6"))
        val studentID07 = dao.insert(Student(studentDetails = "student7"))
        val studentID08 = dao.insert(Student(studentDetails = "student8"))

        dao.insert(StudentCourseMap(studentID01,courseId001))
        dao.insert(StudentCourseMap(studentID01,courseId003))
        dao.insert(StudentCourseMap(studentID01,dao.getCourseIdByCourseDetails("Course5")))

        dao.insert(StudentCourseMap(studentID02,courseId002))
        dao.insert(StudentCourseMap(studentID02,courseId004))

        dao.insert(StudentCourseMap(studentID03,courseId001))
        dao.insert(StudentCourseMap(studentID04,courseId002))
        dao.insert(StudentCourseMap(studentID05,courseId003))
        dao.insert(StudentCourseMap(studentID06,courseId004))
        dao.insert(StudentCourseMap(studentID07,dao.getCourseIdByCourseDetails("Course2")))
        dao.insert(StudentCourseMap(studentID08,dao.getCourseIdByCourseDetails("Course5")))

        for(cwtas: CourseWithTeacherAndStudents in dao.getAllCoursesWithTeacherAndStudents()) {
            Log.d("DBINFO","Course is ${cwtas.course.courseDetails}. Teacher is ${cwtas.teacher.teacherDetails}. There are ${cwtas.studentList.size} students. they are:-")
            for (s: Student in cwtas.studentList) {
                Log.d("DBINFO","\tStudent Details are ${s.studentDetails} id is ${s.studentId}")
            }
        }
    }
}

运行上述内容（仅一次）时输出到日志的结果是：-

2022-03-23 19:01:57.337 D/DBINFO: Course is Course1. Teacher is teacher1. There are 2 students. they are:-
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student1 id is 1
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student3 id is 3
2022-03-23 19:01:57.337 D/DBINFO: Course is Course2. Teacher is teacher1. There are 3 students. they are:-
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student2 id is 2
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student4 id is 4
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student7 id is 7
2022-03-23 19:01:57.337 D/DBINFO: Course is Course3. Teacher is teacher2. There are 2 students. they are:-
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student1 id is 1
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student5 id is 5
2022-03-23 19:01:57.337 D/DBINFO: Course is Course4. Teacher is teacher3. There are 2 students. they are:-
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student2 id is 2
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student6 id is 6
2022-03-23 19:01:57.338 D/DBINFO: Course is Course5. Teacher is teacher5. There are 2 students. they are:-
2022-03-23 19:01:57.338 D/DBINFO:   Student Details are student1 id is 1
2022-03-23 19:01:57.338 D/DBINFO:   Student Details are student8 id is 8

It is hard to imagine what the schema is. But it would appear that you do not have a many to many relationship as you would accomplish this with what is known by terms such as mapping table, reference table, associative table .... (all basically the same thing)

Such a table typically has two columns one that references/maps one of the tables (e.g. student) and the other column references/maps to the other table such as the course.

Anyway here is an example of what you appear to be trying to achieve. It has a table for Students, a table for Teachers and a table for Courses. The course table includes a column that references/maps the teacher (assuming one teacher per course). Lastly there is the mapping table that caters for the many-many relationship that allows a course to have many students and for a student to be in many courses.

The Entities are :-

Teacher

@Entity(tableName = "teacher_table")
data class Teacher(
    @PrimaryKey
    var teacherId: Long? = null,
    var teacherDetails: String
)

Student

@Entity(tableName = "student_table")
data class Student(
    @PrimaryKey
    var studentId: Long? = null,
    var studentDetails: String
)

Course

@Entity(tableName = "course_table",
    /* Foreign Keys optional but enforces referential integrity */
    foreignKeys = [
        ForeignKey(
            entity = Teacher::class,
            parentColumns = ["teacherId"],
            childColumns = ["teacherIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Course(
    @PrimaryKey
    var courseId: Long? = null,
    var courseDetails: String,
    @ColumnInfo(index = true)
    var teacherIdMap: Long /* if only one teacher for the course */
)

Last the mapping table Entity StudentCourseMap

@Entity(tableName = "student_course_map_table",
    primaryKeys = ["studentIdMap","courseIdMap"],
    foreignKeys = [
        ForeignKey(entity = Student::class,
            parentColumns = ["studentId"],
            childColumns = ["studentIdMap"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey( entity = Course::class,
            parentColumns = ["courseId"],
            childColumns = ["courseIdMap"],
            onDelete =  ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class StudentCourseMap(
    var studentIdMap: Long,
    @ColumnInfo(index = true)
    var courseIdMap: Long
)

To support the above and get a Course, with the Teacher and with All the Students in the course there is a POJO class CourseWithTeacherAndStudents

data class CourseWithTeacherAndStudents(

    @Embedded
    var course: Course,
    @Embedded
    var teacher: Teacher,
    @Relation(
        entity = Student::class,
        parentColumn = "courseId",
        entityColumn = "studentId",
        associateBy = Junction(
            StudentCourseMap::class,
            parentColumn = "courseIdMap",
            entityColumn = "studentIdMap"
        )
    )
    var studentList: List<Student>
)

This is similar to the class in your question (but also includes the course details) and is along the lines of what you want to retrieve from the database.

data class CourseWithTeacherAndStudents(

    @Embedded
    var course: Course,
    @Embedded
    var teacher: Teacher,
    @Relation(
        entity = Student::class,
        parentColumn = "courseId",
        entityColumn = "studentId",
        associateBy = Junction(
            StudentCourseMap::class,
            parentColumn = "courseIdMap",
            entityColumn = "studentIdMap"
        )
    )
    var studentList: List<Student>
)

To utilise the above the AllDAO is an @Dao annotated abstract class (can be an interface but if you want to process data from your own JOINS and abstract class can have functions with bodies ).

@Dao
abstract class AllDAO {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(teacher: Teacher): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(student: Student): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(course: Course): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(courseMap: StudentCourseMap): Long
    @Query("SELECT courseId FROM course_table WHERE coursedetails=:courseDetails ")
    abstract fun getCourseIdByCourseDetails(courseDetails: String): Long
    @Transaction
    @Query("SELECT * FROM course_table JOIN teacher_table ON course_table.teacherIdMap = teacher_table.teacherId")
    abstract fun getAllCoursesWithTeacherAndStudents(): List<CourseWithTeacherAndStudents>
    
}

Putting it all together with a working example

with a pretty standard @Database annotated class (in this case for brevity and convenience .allowMainThreadQueries has be used)
is the following in an Activity

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDAO
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAO()

        val teacherId01 = dao.insert(Teacher(teacherDetails = "teacher1"))
        val teacherId02 = dao.insert(Teacher(teacherDetails = "teacher2"))
        val teacherId03 = dao.insert(Teacher(teacherDetails = "teacher3"))
        val teacherId04 = dao.insert(Teacher(teacherDetails = "teacher4")) // not used
        val courseId001 = dao.insert(Course(courseDetails = "Course1", teacherIdMap = teacherId01))
        val courseId002 = dao.insert(Course(courseDetails = "Course2", teacherIdMap = teacherId01))
        val courseId003 = dao.insert(Course(courseDetails = "Course3", teacherIdMap = teacherId02))
        val courseId004 = dao.insert(Course(courseDetails = "Course4", teacherIdMap = teacherId03))
        dao.insert(Course(courseDetails = "Course5", teacherIdMap = dao.insert(Teacher(teacherDetails = "teacher5"))))
        val studentID01 = dao.insert(Student(studentDetails = "student1"))
        val studentID02 = dao.insert(Student(studentDetails = "student2"))
        val studentID03 = dao.insert(Student(studentDetails = "student3"))
        val studentID04 = dao.insert(Student(studentDetails = "student4"))
        val studentID05 = dao.insert(Student(studentDetails = "student5"))
        val studentID06 = dao.insert(Student(studentDetails = "student6"))
        val studentID07 = dao.insert(Student(studentDetails = "student7"))
        val studentID08 = dao.insert(Student(studentDetails = "student8"))

        dao.insert(StudentCourseMap(studentID01,courseId001))
        dao.insert(StudentCourseMap(studentID01,courseId003))
        dao.insert(StudentCourseMap(studentID01,dao.getCourseIdByCourseDetails("Course5")))

        dao.insert(StudentCourseMap(studentID02,courseId002))
        dao.insert(StudentCourseMap(studentID02,courseId004))

        dao.insert(StudentCourseMap(studentID03,courseId001))
        dao.insert(StudentCourseMap(studentID04,courseId002))
        dao.insert(StudentCourseMap(studentID05,courseId003))
        dao.insert(StudentCourseMap(studentID06,courseId004))
        dao.insert(StudentCourseMap(studentID07,dao.getCourseIdByCourseDetails("Course2")))
        dao.insert(StudentCourseMap(studentID08,dao.getCourseIdByCourseDetails("Course5")))

        for(cwtas: CourseWithTeacherAndStudents in dao.getAllCoursesWithTeacherAndStudents()) {
            Log.d("DBINFO","Course is ${cwtas.course.courseDetails}. Teacher is ${cwtas.teacher.teacherDetails}. There are ${cwtas.studentList.size} students. they are:-")
            for (s: Student in cwtas.studentList) {
                Log.d("DBINFO","\tStudent Details are ${s.studentDetails} id is ${s.studentId}")
            }
        }
    }
}

The result output to the log when running the above (just once) is :-

2022-03-23 19:01:57.337 D/DBINFO: Course is Course1. Teacher is teacher1. There are 2 students. they are:-
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student1 id is 1
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student3 id is 3
2022-03-23 19:01:57.337 D/DBINFO: Course is Course2. Teacher is teacher1. There are 3 students. they are:-
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student2 id is 2
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student4 id is 4
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student7 id is 7
2022-03-23 19:01:57.337 D/DBINFO: Course is Course3. Teacher is teacher2. There are 2 students. they are:-
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student1 id is 1
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student5 id is 5
2022-03-23 19:01:57.337 D/DBINFO: Course is Course4. Teacher is teacher3. There are 2 students. they are:-
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student2 id is 2
2022-03-23 19:01:57.337 D/DBINFO:   Student Details are student6 id is 6
2022-03-23 19:01:57.338 D/DBINFO: Course is Course5. Teacher is teacher5. There are 2 students. they are:-
2022-03-23 19:01:57.338 D/DBINFO:   Student Details are student1 id is 1
2022-03-23 19:01:57.338 D/DBINFO:   Student Details are student8 id is 8

回复收藏 0 原文

~没有更多了~