How to write a query for HQL?

Question

I have a project: Monitoring student grades. I have some entities. There is:

@Entity
@Table(name = "clazzes")
public class Clazz extends BaseEntity{

    @NotEmpty
    @Column(name = "number")
    private String number;

    @NotEmpty
    @Column(name = "letter")
    private String letter;

    @JsonIgnore
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "clazz", fetch = FetchType.EAGER)
    private Set<Student> students;

    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="school_id")
    private School school;

    // getters and setters
}

@Entity
@Table(name = "students")
public class Student extends Person {

    @JsonIgnore
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "parents_students", joinColumns = @JoinColumn(name = "student_id"),
            inverseJoinColumns = @JoinColumn(name = "parent_id"))
    private List<Parent> parents;

    @NotNull
    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = "clazz_id")
    private Clazz clazz;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "student", fetch = FetchType.EAGER)
    private Set<Grade> grades;

    // getters and setters
}


@Entity
@Table(name = "grades")
public class Grade extends BaseEntity{

    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name = "shedule_id")
    private Shedule shedule;

    @NotNull
    @JsonIgnore
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="student_id")
    private Student student;

    @Column(name = "task")
    private String task;

    @Column(name = "mark")
    private Integer mark;

    // getters and setters
}


@Entity
@Table(name = "shedule")
public class Shedule extends BaseEntity implements Comparable<Shedule>{

    @NotNull
    @Column(name = "date")
    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = "dd.MM.yyyy")
    @DateTimeFormat(pattern = "dd.MM.yyyy")
    private Date date;

    @NotNull
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="period_id")
    private Period period;

    @NotNull
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="subject_id")
    private Subject subject;

    @NotNull
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="clazz_id")
    private Clazz clazz;

    @NotNull
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="teacher_id")
    private Teacher teacher;

    @Column(name = "job")
    private String job;

    @JsonIgnore
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "shedule", fetch = FetchType.EAGER)
    private Set<Grade> grades;

    // getters and setters
}

Please tell me how to get an HQL query for the following data: Schedule, Student, Grade.

I am trying to write a query like this:

Query query = this.em.createQuery("SELECT DISTINCT shedule, students, grade " +
                "FROM Shedule shedule " +
                "INNER JOIN shedule.clazz clazz " +
                "INNER JOIN clazz.students students " +
                "LEFT JOIN students.grades grade with (grade.shedule.id = shedule.id)");

but I get the following error due to the additional conditions "with (grade.sment.id = shedule.id)":

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elements [SELECT DISTINCT shedule, students, grade FROM com.vizaco.onlinecontrol.model.Shedule shedule INNER JOIN shedule.clazz clazz INNER JOIN clazz.students students LEFT JOIN students.grades grade with (grade.shedule.id = shedule.id)

Can I write an HQL query if this project architecture will be used? Or is it necessary to change the architecture of the project? Or do you need to write your own request?

Answer 1

SELECT DISTINCT shedule, students, grade
      FROM Shedule shedule
      INNER JOIN shedule.clazz clazz 
      INNER JOIN clazz.students students
      LEFT JOIN students.grades grade
      WHERE grade IS NULL OR grade.shedule.id = shedule.id