Spring boot JPA many to many query | find all courses courses which are attended students-CodePudding

For example, we have students S1, S2, S3, S4 and courses C1, C2 and C3.

Students S1, S2 attent C1

Students S1, S2, S3 attend C2.

Find all courses, in which S1 and S2 attend together

(expected answer C1 and C2)

Would also be grateful if answer can be shown how to implement in Spring JPA / CRUD repository.

Entities:

class Course {
    @Id
    private String id;
    private String name;
    
    @ManyToMany(fetch = FetchType.EAGER) //debugging purpouses
    @JoinTable(name = "course_students",
            joinColumns = @JoinColumn(name = "course_id"),
            inverseJoinColumns = @JoinColumn(name = "student_id"))
    Set<Student> students;
}

class Student {
    @Id
    String id;
    String firstName;
    String middleName;
    String lastName;
    String phoneNumber;
    String email;
    String avatar;
    int age;

    @ManyToMany(fetch = FetchType.EAGER, mappedBy = "students")
    Set<Course> courses;
}

CodePudding user response：

@Query(Select c from Course c group by c having :student_lst in array_arg(c.student))
List<Course> findCoursesByStudents(@param("student_lst") List<Student> student_lst);

You can use above query for your task.In this query first students group by the courses they attend.After get grouped filter out the courses where the student_lst is subset of all student list grouped by course.

CodePudding user response：

Use MEMBER OF :

select c from Courses c where :student1 member of c.students and :student2 member of c.students