'Using sub-select in FROM clause inside JPA @NamedQuery

In my app I need to use @NamedQuery to find the type of the most frequent operation assigned to specific account

@Entity
@Table(name="\"ACCOUNTOPERATION\"")
@NamedQuery(name="AccountOperation.findTypeOfMostFrequentOperation", query="" +
        "SELECT ao.type from AccountOperation ao WHERE ao.account.id = ?1 " +
        "GROUP BY ao.type HAVING COUNT(ao) = (" +
            "SELECT MAX(typeCountQuery.typeCount) " +
            "FROM (" +
                "SELECT COUNT(aop) as typeCount " +
                "FROM AccountOperation aop WHERE aop.account.id = ?1 GROUP BY aop.type" +
            ") as typeCountQuery" +
        ")"
)
public class AccountOperation {

    @ManyToOne
    private Account account;
    private BigDecimal amount;
    private OperationType type;
...

Right after FROM clause at '(' character, which begins typeCountQuery's body I'm getting

')', ',', GROUP, HAVING, IN, WHERE or identifier expected, got '('

I've read that JPA does not support sub-selects in the FROM clause, so is there any way to rewrite SQL code to still use it in @NamedQuery?

I'm using IntelliJ IDE with H2 DB and with eclipselink and javax.persistence in dependencies.

sqlmavenjpah2jpql


Solution 1:[1]

The type with a highest count returns the following query

select type
from AccountOperation
where id = ?
group by type
order by count(*) desc
fetch first 1 ROWS only

You should be anyway avare of the existence of ties, i.e. more types with the identical maximal count and should make some thought how to handle them.

I.e. in Oracle you may say fetch first 1 ROWS WITH TIES to get all the types with tha maximal count.

Solution 2:[2]

Link to source

In JPQL, you cannot use subqueries. To resolve this issue, you need to use some keywords like ALL, ANY, which work similiar.

So in your situation it could be:

@NamedQuery(name="AccountOperation.findTypeOfMostFrequentOperation", query="" +
    "SELECT ao.type from AccountOperation ao WHERE ao.account.id = ?1 " +
    "GROUP BY ao.type HAVING COUNT(ao) >= ALL (" +
            "SELECT COUNT(aop) as typeCount " +
            "FROM AccountOperation aop WHERE aop.account.id = ?1 GROUP BY aop.type)"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Marmite Bomber
Solution 2 KosztDojscia

Related Questions