Блог

У меня есть 3 таблицы: студент, предмет, сопоставление student_subject_mapping. студент и предмет имеют отношение «многие ко многим», и третья таблица содержит сопоставление и отметки по этому конкретному предмету.

 Student:
| id   | name   | gpa   |
|:---- |:------:| -----:|
| 1    | Tom    | 7.0   |
| 2    | Jerry  | 8.0   |
| 3    | Popeye | 7.5   |

Subject:
| id   | name             |
|:---- |:----------------:|
| 1    | Physics          |
| 2    | Chemistry        |
| 3    | Math             |
| 4    | Computer Science |

student_subject_mapping:
| student_id | subject_id | score |
|:---------- |:----------:| -----:|
| 1          | 1          | 5.0   |
| 1          | 2          | 6.0   |
| 1          | 3          | 7.5   |
| 1          | 4          | 8.0   |
| 2          | 2          | 6.0   |
| 2          | 3          | 7.0   |
| 2          | 4          | 8.0   |
| 3          | 1          | 7.0   |
| 3          | 3          | 6.0   |
| 3          | 4          | 8.5   |
 

Я хочу отбирать студентов по предметам , скажем, физике, химии, математике, информатике, поэтому комбинированные запросы будут примерно такими:

1. Выберите студентов со всеми 4 предметами и оценками > 5 и >средний балл > 6
2. Выберите студентов по крайней мере с 3 из вышеперечисленных предметов и баллами > 6 и >средний балл > 7
3. Выберите студентов с по крайней мере «n» из вышеперечисленных предметов и баллами > 7 и >средний балл > 7

Где предметы, «n», оценка и средний балл являются динамическими

Student.java

 @Getter
@Setter
@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
public class Student implements BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private Double gpa;

    @OneToMany(mappedBy = "student", cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.LAZY)
    private Set<StudentSubjectMapping> studentSubjectMapping = new HashSet<>();

}
 

Subject.java

 @Getter
@Setter
@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
public class Subject implements BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

@JsonIgnore
    @OneToMany(mappedBy = "subject")
    private Set<StudentSubjectMapping> studentSubjectMapping = new HashSet<>();
    
}
 

 @Entity
@Table
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class StudentSubjectMapping{

    @EmbeddedId
    StudentSubjectMappingId StudentSubjectMappingId;

    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("studentId")
    private Student student;

    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("subjectId")
    private Subject subject;

    private Double score;
}
 

 @Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
@Embeddable
public class StudentSubjectMappingId implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long studentId;
    private Long subjectId;
}
 

Я пробовал разные вещи(ранее без EmbeddedId), но ничего не помогает:

 @Query("from Student student where student.subjects in :subjects group by student.subjects HAVING count(DISTINCT student.subjects) =:count ")

@Query("SELECT new com.nsia.model.Form(f.id, f.name, f.description, f.createdAt, g, COUNT(i.id)) from Form f "  
            "LEFT JOIN f.instances JOIN f.groups g WHERE f.groups IN (?1) group by f.id")
            
@Query("from Student student LEFT JOIN student.subjects subjects WHERE subjects.id in :subjects group by student.subjects HAVING count(DISTINCT student.subjects.size) =:count ")

@Query("from Student student where student.id not in (SELECT stud.id from Student stud where stud.subjects not in (:subjects))")

@Query("select student from Student as student "  
            "where student.id not in "  
            "(select students.id from Student as students "  
            "join students.subjects as subjects "  
            "where subjects.id not in (:subjectIds))")

@Query(value = "select students.id from Student as students "  
            "left join fetch students.subjects as subjects "  
            "where subjects.id not in (:subjectIds) and students.id in (1,2,3)",
    countQuery = "select count (student) from Student student left join student.subjects")
    List<Long> getstudentIds(@Param("subjectIds") List<Long> subjectIds);
 

Вы могли бы использовать такой запрос:

 @Query("select student from Student as student "  
        "where student.gpa >= :minGpa and :count >= "  
        "(select count(*) from student.studentSubjectMapping as m "  
        "where m.subject.id in (:subjectIds) "   
        "and m.score >= :minScore)")
List<Long> getStudents(@Param("count") long count, @Param("minGpa") double minGpa, @Param("minScore") double minScore, @Param("subjectIds") List<Long> subjectIds);

default List<Long> getStudents(double minGpa, double minScore, List<Long> subjectIds) {
    return getStudents(subjectIds.size(), minGpa, minScore, subjectIds);
}