JPQL Order By Average from a second table
Background
I have two objects persisted using Hibernate. Product and Vote.
The Product object contains a list of votes, such as
public class Product {
@javax.persistence.Id
@javax.persistence.GeneratedValue
public Long id;
String name;
@OneToMany(mappedBy = "product", cascade = CascadeType.ALL)
List<Vote> votes;
}
the Vote object simply holds the score, and bi-directional link to the product.
public class Vote {
@javax.persistence.Id
@javax.persistence.GeneratedValue
public Long id;
Short score;
@ManyToOne
Product product;
}
This works fine, and in my database I can see the products and the votes as expected. Now, I want to write a JPQL query to order the products i开发者_JS百科n order of the highest average votes.
I have achieved this using plain SQL as follows
select p.name
from product p
order by (select avg(score) from vote v where p.id = v.product_id) desc;
Question
I can't seem to get this to work in my JPQL query. I just get an error saying "unexpected AST node".
The JPQL I am using is
select p from Product p
order by (select avg(score) from Vote v where p.id = v.product) desc
does JPQL not support inner select statements?
This might work:
SELECT p FROM Product p LEFT JOIN P.votes v
GROUP BY p ORDER BY AVG(v.score) desc
精彩评论