Nested query with HQL / Hibernate

问题:

For what I have read, in some cases it is not possible to nest subqueries in HQL / Hibernate.

Let's say that I have a table of articles with a price, that belong to a group. We want to add them all, but we can only add up to a limit per group of articles. Actually, the table is denormalized, so that we already have the maximum amount for the group in the article table.

So the SQL would be as simple as :

SELECT SUM(case when max_amount is null then price
when price<max_amount then price
else max_amount end)
FROM
(SELECT
SUM(price) as price, group_id, max_amount
FROM articles
GROUP BY group_id, max_amount
)

Obviously, my data model is more complicated, but this is my main problem.

I wonder how can I rewrite this in HQL as I guess I am not allowed to nest one subquery into the other.

Thanks everybody.

回答1:

I was reluctant to do this in this way, but after some research I could only think about using the SQL query as it is.

Something like :

String sQuery = "SELECT SUM(case when max_amount is null then price ...
final org.hibernate.SQLQuery query = session.createSQLQuery(sQuery);
List results = query.list();

I can't think about Hibernate handling that nested query with HQL.