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));

Answer 2

Since this was posted with a MySQL tag, I'm going to answer the query you need (for others) and then hopefully you can figure out the QueryDSL code from that:

SELECT * from Certificate 
    WHERE date > "2014-01-01 00:00:00" and date < "2015-01-01 00:00:00" AND 
    id IN (SELECT id from Employee 
           WHERE first_name LIKE '%Name%' 
                 || last_name LIKE '%LName%')

From the command line with output:

mysql> SELECT * from Certificate 
    ->         WHERE date > "2014-01-01 00:00:00" and date < "2015-01-01 00:00:00" AND 
    ->         id IN (SELECT id from Employee 
    ->                WHERE first_name LIKE '%Name%' 
    ->                      || last_name LIKE '%LName%');
+----+--------------------+---------------------+--------+
| id | name               | date                | fk_emp |
+----+--------------------+---------------------+--------+
|  1 | FirstName LastName | 2014-02-01 00:00:00 |  11111 |
+----+--------------------+---------------------+--------+
1 row in set (0.00 sec)

Sorry, not familiar with QueryDSL so you'll need to modify your code to match that query.

Here's the schema I used to test this with:

CREATE TABLE `Certificate` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `date` datetime NOT NULL,
  `fk_emp` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Certificate` (`id`, `name`, `date`, `fk_emp`) VALUES
(1, 'FirstName LastName',   '2014-02-01 00:00:00',  11111);

CREATE TABLE `Employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Employee` (`id`, `first_name`, `last_name`) VALUES
(1, 'FirstName',    'LastName');