I am using Hibernate 3.3.2.GA with a PostgreSQL database. I have a user_scores table which looks like (irrelevant portions omitted):
Code:
user_scores (
user_id int,
type varchar,
score int
)
I would like to count the number of users of each type with positive, negative, and zero total score. With SQL I would do this like:
Code:
select type, sign_score, count(*) from (select type, user_id, sign(sum(score)) as sign_score from user_scores [where restrictions] group by type, user_id) as _ group by type, sign_score;
However, I am bound to the Criteria API because the application has very complex restriction logic in place of [where condition] in the above query. I would like to avoid duplicating this logic with straight SQL or migrating the application away from the Criteria API.
I understand how to use projections in criteria queries in more typical ways, but does the Criteria API support nesting projections in outer and inner queries like in the SQL query? Alternately, I know that this board isn't for SQL help, but is there any way to rewrite this query without a subquery to be compatible with Hibernate's projections?
Note that I did manage to solve this problem with what appears to be an ugly hack but I would rather use a supported solution. The hack is:
Code:
DetachedCriteria query = ...
query.setProjection(Projections.sqlGroupProjection(
"type, sign_score, count(*) as results from (" +
"select {alias}.type, {alias}.user_id, sign(sum({alias}.score)) as sign_score",
// Hibernate query goes here
"{alias}.type, {alias}.user_id" +
") as _ group by type, sign_score",
new String[] {"type, sign_score, results"},
new Type[] {Hibernate.STRING, Hibernate.INTEGER, Hibernate.INTEGER}
));
Thanks!
Login
FAQ
Search