Hi all,
I have a complex sql query but the simplest form of it look likes below (I want to retrieve last 10 records' distribution based on status, e.g.:there are 4 inactive and 6 active users in last 10 records):
Code:
SELECT status, count(id) FROM
(SELECT id, status FROM `User` ORDER BY id DESC LIMIT 10) t1
group by status order by status desc
How could you write this query in Criteria Api. Here what I've done, but the problem is, limit is not working in subquery.
Code:
DetachedCriteria subQuery = DetachedCriteria.forClass(User.class);
subQuery.getExecutableCriteria(getCurrentSession()).setMaxResults(10);
subQuery.setProjection(Projections.projectionList()
.add(Projections.property("id"))
.add(Projections.property("status")))
.addOrder(Order.desc("id"));
Criteria criteria = getCurrentSession().createCriteria(User.class);
criteria.setProjection(Projections.projectionList()
.add(Projections.property("status"), "status")
.add(Projections.count("id"), "count")
.add(Projections.groupProperty("status")))
.addOrder(Order.desc("status"))
.setResultTransformer(Transformers.aliasToBean(UserViewBean.class));
criteria.add(Subqueries.exists(subQuery));
Any help will be appreciated.
Regards
Login
FAQ
Search
