JPA multi fetch with @OrderColumn return multiple null value

Question

Have 3 entities
Group

@Entity
@Table(name = "`group`")
public class Group implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_group")
    private Long id;

    @OneToMany(mappedBy = "group",cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    @OrderColumn(name = "id_student")
    private List<Student> students = new ArrayList<>();

    @ManyToOne
    @JoinColumn(name = "id_faculty")
    private Faculty faculty;
     .....getters/setters 
}

Student

@Entity
@Table(name = "student")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id", scope = Long.class)
public class Student implements Serializable {
    @Id
    @Column(name = "id_student")
    private Long id;

    ......

    @OneToMany(mappedBy = "student",cascade = CascadeType.ALL,fetch = FetchType.EAGER)
    private List<Rating> ratings = new ArrayList<>();

    @ManyToOne
    @JoinColumn(name = "id_okr")
    private OKR okr;

    @ManyToOne
    @JoinColumn(name = "id_group")
    private Group group;
.....getters/setters 
}

Rating

@Entity
@Table(name = "rating")
public class Rating implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id_rating")
    private Long id;

    @Temporal(TemporalType.DATE)
    @Column
    private Date date;

    @ManyToOne
    @JoinColumn(name = "id_student")
    private Student student;

    @ManyToOne
    @JoinColumn(name = "id_paragraph")
    private Paragraph paragraph;
    .....getters/setters 
}

JPA Query

@Query(value = "SELECT g FROM Group g INNER JOIN FETCH g.students s LEFT JOIN FETCH s.ratings r WHERE g.id = :id AND s.group.id = g.id AND (r.student.id = s.id AND r.date BETWEEN :startMonth and :endMonth OR r IS NULL) GROUP BY s.id")
Group findGroupByStudentGroupId(@Param("id") Long id ,@Param("startMonth") Date startMonth, @Param("endMonth") Date endMonth );

I have the student with id 8000 and after extracting query the result list contains 8001 elements which contain 8 students that I have and 7993 null values. If I remove annotation @OrderColumn I have MultiBagException(cannot simultaneously fetch). If add @OrderColumn to rating @OneToMany association in the entity Student I have null values in Rating collection and in Student collection. For me, the logic of @OrderColumn which return null values as biggest id in collection seems very strange. Is any way how to solve it?
Hibernate version 5.1.0 Final
Spring Data JPA 1.8.2

@PoojaAggarwal I want to fetch All students of the group with their ratings in scope month or fetch rating as null. — Taras Danylchenko
2021 and I still have same problem. What did you do to fix this? — Nikki

Pooja Aggarwal Pooja Aggarwal · Accepted Answer · 2019-03-19T11:55:46

As you are trying to fetch students with particular group and ratings, you can have your query fetch from student entity and have join on Ratings entity. You don't have to explicitly add join for group entity as it is already in many to one mapping with student.

You can Change your query as follows:

@Query(value = "SELECT s FROM Student s LEFT JOIN s.ratings r WHERE s.group.id = :id  AND (r.date BETWEEN :startMonth and :endMonth OR r IS NULL) GROUP BY s.id")
List<Student> findGroupByStudentGroupId(@Param("id") Long id ,@Param("startMonth") Date startMonth, @Param("endMonth") Date endMonth );

JPA multi fetch with @OrderColumn return multiple null value

1 Answers