Subquery jpql order throws unexpected AST node exception

Question

I translated the working (postgre) sql query into jpql, but hibernate throws

org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node exception

These are my main model classes:

@Entity public class Piece { @Id @GeneratedValue public Long id; @ManyToOne public AUser user; public long index; ... } @Entity public class Vote { @Id @GeneratedValue public Long id; @ManyToOne public AUser receiver; ... } @Entity public class AUser { @Id @GeneratedValue public Long id; @OneToMany(mappedBy="receiver", cascade=CascadeType.ALL) public List<Vote> receivedVotes; ... } 

Here is my jpql request:

 String query = "select p from Piece p order by (select count(v.receiver) from Vote v where v.receiver.id=p.user.id) desc, p.index"; 

Can someone explain the exception why this happens and how to modify the request to avoid it. Thanks!

Answer 1

JPQL does not support subqueries in order by . If I understand your request correctly, you can try something like this:

 select p from Piece p left join p.user.receivedVotes rv group by p order by count(rv) desc, p.index