Hello all, I have a special case where I would like to group by a subselect. Here is the code
Code:
public List findAverageScoreByMember(int maxResults) {
log.debug("finding Contentrating instance by member");
try {
String queryString = "select (select d.member from Document d where d.documentId = model.contentId), sum(model.score), count(model), ( sum(model.score) / count(model) ) from Contentrating as model group by (select d.member from Document d where d.documentId = model.contentId) order by ( sum(model.score) / count(model) ) DESC";
HibernateTemplate hibernateTemplate = getHibernateTemplate();
hibernateTemplate.setMaxResults(maxResults);
List results = hibernateTemplate.find(queryString);
return results;
} catch (RuntimeException re) {
log.error("find by property name failed", re);
throw re;
}
}
I need to group by a subselect, the reason is because my tables to not have a link between them therefore no join possible. I subselect to get the member ID and would need to group by this ID. I have tried with an alisas, even tried with the whole subselect statement in the group by without any luck. Anyone have any ideas?
PS : I am using hibernate template with Spring, therefore no need for the callback code and stuff
Login
FAQ
Search