Write queryDSL predicate query for oneTomany relation based query

Question

I am using spring-data, QueryDSL and MySQL.

Main aim of question is to know how to do a such a query in queryDSL way. Given example is just a simple example to give idea.

Say for example, there are two tables Employee and Certificate. Relation between two is ONE (Employee) to MANY (Certificate)

Following are tables,

Employee (id, first_name, last_name);
Certificate (id, name, date, fk_emp);

What should be the QueryDSL predicate for

Returning all employees with name contains (in first_name and last_name) and from that result whose certification between date 22-12-2014 to 22-12-2015

I tried it but could not get how I can iterate over each certificate of each employee in QueryDSL way and return list of employees.

Your response will be highly appreciated !!

EDIT

Following are the entities,

@Entity
@Table(name = "EMPLOYEE")
class Employee {
  @Id
  @Column(name = "ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Column(name = "FIRST_NAME")
  private String firstName;

  @Column(name = "LAST_NAME")
  private String lastName;

  @OneToMany(mappedBy = "employee", cascade = CascadeType.ALL)
  private List<Certificate> certificates = new ArrayList<>();
}

@Entity
@Table(name = "CERTIFICATE")
class Certificate {
  @Id
  @Column(name = "ID")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

  @Column(name = "CERTIFICATE_NAME")
  private String certificateName;

  @Column(name = "DATE")
  private Date date;

  @ManyToOne
  @JoinColumn(name = "REF_EMPLOYEE")
  private Employee employee;
}

Answer 1

The easiest thing to do is reverse it and query for certificates which is straightforward and you can then get the employee from the returned certificates.

QCertificate certificate = QCertificate.certificate;
BooleanExpression a = certificate.date.between(d1, d2);
BooleanExpression b = certificate.employee.forename.eq("name").
      or(certificate.employee.surname.eq("name"));

certificateRepository.findAll(a.and(b));

If you want to query for Employees then try the following which is against QueryDSL version 4.1.3.

    QEmployee employee = QEmployee.employee;
    QCertificate certificate = QCertificate.certificate;

    BooleanExpression a = employee.forename.eq("name").or(employee.surname.eq("name"));

    BooleanExpression b = employee.certificates.contains(
        JPAExpressions.selectFrom(certificate).
          where(certificate.employee.eq(employee).
           and(certificate.date.between(d1, d2))));

    userRepository.findAll(a.and(b));