How can have many-to-many result as list in pure SQL query

Question

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

Answer 1

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