开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜