Complex JPQL query to list last created file from all archives

Question

I have this DB structure:

CREATE TABLE `archive` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `employee_id` bigint(20) NOT NULL,
  `subcategory_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_pnk12l4936a21pst3p696mgt6` (`name`),
  KEY `fk_archive_employee` (`employee_id`),
  KEY `fk_archive_subcategory` (`subcategory_id`)
)


CREATE TABLE `archive_file` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `created` datetime NOT NULL,
  `edition` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `expire` date NOT NULL,
  `history` text COLLATE utf8_unicode_ci NOT NULL,
  `mime_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `archive_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_lgh85dj5mtjkevbkmgl8q0rso` (`name`),
  KEY `fk_archive_file_archive` (`archive_id`)
)

And this java entities:

Archive.java

@Entity
@Table(name = "archive")
public class Archive extends AbstractEntity {

   @NotEmpty
   @Size(max = ConstantNumeric.TWO_HUNDRED)
   @Column(name = "name", length = ConstantNumeric.TWO_HUNDRED, unique = true)
   private String name;

   @NotEmpty
   @ManyToOne
   @JoinColumn(name = "subcategory_id")
   @ForeignKey(name = FK + "subcategory")
   private ArchiveCategory subcategory;

   @NotEmpty
   @OneToMany(mappedBy = "archive", cascade = { CascadeType.PERSIST, CascadeType.MERGE })
   private List<ArchiveFile> files;

   @NotEmpty
   @ManyToOne
   @JoinColumn(name = "employee_id")
   @ForeignKey(name = FK + "employee")
   private Employee responsible;

   @NotEmpty
   @AuditJoinTable(inverseJoinColumns = @JoinColumn(name = "department_id"))
   @ManyToMany
   @JoinTable(name = "archive_has_department", joinColumns = @JoinColumn(name = "archive_id"),
            inverseJoinColumns = @JoinColumn(name = "department_id"))
   @ForeignKey(name = FK + "has_department", inverseName = FK + "department")
   private List<Department> departments;

...Getters/Settters

ArchiveFile.java

@Entity
@Table(name = "archive_file")
public class ArchiveFile extends AbstractEntity {

   @NotEmpty
   @Size(max = ConstantNumeric.TWO_HUNDRED)
   @Column(name = "name", unique = true, length = ConstantNumeric.TWO_HUNDRED, updatable = false)
   private String name;

   @NotEmpty
   @Size(max = ConstantNumeric.FOUR)
   @Column(name = "edition", length = ConstantNumeric.FOUR, updatable = false)
   private String edition;

   @NotEmpty
   @Temporal(TemporalType.DATE)
   @Column(name = "expire", updatable = false)
   private Date expire;

   @NotEmpty
   @Temporal(TemporalType.TIMESTAMP)
   @Column(name = "created", updatable = false)
   private Date created;

   @NotEmpty
   @Column(columnDefinition = "text", updatable = false)
   private String history;

   @NotEmpty
   @ManyToOne(cascade = CascadeType.ALL, optional = false)
   @JoinColumn(name = "archive_id", referencedColumnName = "id")
   @ForeignKey(name = "fk_archive_file_archive")
   private Archive archive;

   @NotEmpty
   @Column(name = "mime_type", updatable = false)
   private String mimeType;

...Getters/Settters

With that I need to get all archives with the last created archive_file a SQL Query to do this for example is:

SELECT * FROM archive AS a 
INNER JOIN archive_file as af 
WHERE af.id IN(SELECT a.id FROM 
(SELECT * FROM archive_file AS af WHERE  af.created > '2012-05-30' 
ORDER BY af.created DESC) AS a GROUP BY a.archive_id) AND a.id = af.archive_id

The result is:

+----+---------+--------------------------+-------------+----------------+----+---------+---------------------+---------+------------+-----------------------+--------------+-----------------+------------+
| id | version | name                     | employee_id | subcategory_id | id | version | created             | edition | expire     | history               | mime_type    | name            | archive_id |
+----+---------+--------------------------+-------------+----------------+----+---------+---------------------+---------+------------+-----------------------+--------------+-----------------+------------+
|  1 |       1 | Archive shouldUpdateDAO  |           1 |              4 |  1 |       0 | 2014-05-31 14:41:27 | 1       | 2014-06-04 | history sholdSaveDAO  | document/pdf | SHOULDSAVEDAO   |          1 |
|  2 |       0 | Archive ShouldGetByIdDAO |           1 |              2 |  3 |       0 | 2014-05-30 14:41:27 | 1       | 2014-05-30 | history shouldGetById | document/pdf | SHOUDGETBYIDDAO |          2 |
+----+---------+--------------------------+-------------+----------------+----+---------+---------------------+---------+------------+-----------------------+--------------+-----------------+------------+
2 rows in set (0.00 sec)

But I need to do this with JPQL, i tried this without success:

final String sql = "SELECT archive FROM archive AS a INNER JOIN archive_file as af " +
                   "WHERE af.id in (SELECT a.id FROM (SELECT archive_file FROM archive_file AS af " +
                   "WHERE  af.created > '2012-05-30' ORDER BY af.created DESC) AS a " +
                   "GROUP BY a.archive_id) AND a.id = af.archive_id";

TypedQuery<Archive> query = getEm().createQuery(sql, Archive.class);

A exception is thrown because this is not a valid JPQL query:

javax.ejb.EJBException: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 97 [SELECT archive FROM archive AS a INNER JOIN archive_file as af WHERE af.id in (SELECT a.id FROM (SELECT archive_file FROM archive_file AS af WHERE af.created > '2012-05-30' ORDER BY af.created DESC) AS a GROUP BY a.archive_id) AND a.id = af.archive_id]

There is a way to do this in JPQL? What alternative do you use if not? SQL native query, Criteria or what?

Unknown Unknown · Accepted Answer · 2014-05-30T19:51:35

What do you need the subquerys for?

Wouldn't it work with just the inner join?

SELECT
   * 
FROM
   archive AS a   
INNER JOIN
   archive_file as af ON af.archive_id= a.id  
WHERE
  af.created > '2012-05-30'

Just guessing as I don't have any data.

Edit

A query without the subquerys might look like this:

SELECT
  a.*, af.id as aid, af.created, af.archive_id 
FROM
  archive AS a   
INNER JOIN
  archive_file as af ON af.archive_id= a.id 
LEFT JOIN
  archive_file as af2 ON af2.archive_id= af.archive_id
  AND af2.created > af.created
WHERE
  af.created > '2012-05-30' and
  af2.created is null

sql fiddle here

Complex JPQL query to list last created file from all archives

2 Answers

Edit