How to write a query for HQL?

Question

I have a project: Monitoring student's grades. I have some entities. There are:

@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 HQL query the following data: Shedule, Student, Grade.

I'm trying to write such a query:

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 because of the extra conditions "with (grade.shedule.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)

Is it possible to write HQL query if this architecture of the project will be used? Or is it necessary to change architecture of the project? Or is it necessary to write native query?

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