[FIXED] Is there a way to refer to a HQL subquery column?

Issue

I'd like to perform an HQL query (via Spring Data's @Query) which selects records from message that have value in field a matching the latest message's value, and also matching certain conditions on other fields.

The most obvious approach as I see it would be to use a subquery which maxes records by createTime field, groups them by a and then refer to this column from subquery. However, I can't create the HQL which would compile. Is there a way to refer to subquery's columns, or I should try a different approach to solve this problem?

HQL expression I tried to use:

select m from Message m where m.a = (
    select m2.a, max(m2.createTime) from Message m2
    where ... (additional conditions)
).a

Unfortunately, it threw java.lang.NullPointerException at a compile time.

Solution

This is a typical top N per category query (where N = 1 in your case):

SELECT m
FROM Message m 
WHERE NOT EXISTS (
  FROM Message m2
  WHERE m2.a = m.a
  AND m2.createTime > m.createTime
)

In other words, find all messages for which there doesn't exist a more recent message in the same "category" m.a.

Unfortunately, HQL (at least as of Hibernate 5, maybe 6 has better options) doesn't seem to offer a much better approach than self-anti joining the message table here, which is quite likely to produce bad performance for large tables, even with an index on (a, create_time). If this turns out to be a problem, you can still try a SQL solution. Depending on your RDBMS, you should be able to do this with a single access to the message table.

Answered By - Lukas Eder
Answer Checked By - Cary Denson (JavaFixing Admin)