Search code examples
sqlhibernatejpajpql

Get grouped MAX results of another table in JPQL

I have two entities:

  • Account, containing the attribute accountId
  • ImportRun, which has a attribute runId. There is a one-to-many (bidirectional) relationship from ImportRun to Account.

It is possible to have several Accounts with the same accountId that reference different ImportRuns with different runIds (e.g. one per day).

I want to get the latest runIds for a given set of accountIds (which is a sort of business key, not the hibernate entity key), or even better, a set of Accounts for which each of them references the latest existing ImportRun (so that the runId of this ImportRun has the highest runId of all ImportRuns which are referenced by Accounts with this accountId). In other words: I want to get the most current Accounts. Unfortunately, i can't use native SQL, then it would be quite easy. I already managed to do this sort of thing with only one given accountId with JPQL, but i can't figure out how to group the max results of every runId by several given accountIds.

How can i do this with JPQL?

Solution

  • Next time include code, but to get the latest run IDs for a given set of account IDs:

    SELECT a.accountId, MAX(r.runId) FROM Account a INNER JOIN a.importRun r WHERE a.accountId IN :accountIds GROUP BY a.accountId

    Use this mess of a JPA query to get a list of the most recent accounts:

    SELECT a FROM Account a INNER JOIN a.importRun r WHERE r.runId = (SELECT MAX(ri.runId) FROM Account ai INNER JOIN ai.importRun ri WHERE ai.accountId = a.accountId)

    Hope this helps!