In the Hibernate documentation for HQL the following is shown and I want to do this with the CRITERIA API.

=======================================
SQL functions and aggregate functions are allowed in the having and order by clauses, if supported by the underlying database (eg. not in MySQL).

select cat
from Cat cat
join cat.kittens kitten
group by cat.id, cat.name, cat.other, cat.properties
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc
=======================================

This is the sql I need to replicate with Criteria API:
SELECT ORDER_ITEM.ITEM_ID
FROM ORDER_ITEM
WHERE ORDER_ITEM.STATUS_ID IS NOT NULL
GROUP BY ORDER_ITEM.ITEM_ID
ORDER BY COUNT(ORDER_ITEM.ITEM_ID) DESC

org.hibernate.QueryException: could not resolve property: count(item of: org.nemours.estore.model.CartItem

I have an order item xref table which contains an order id and item id. I need to bring back a list of items in the order of the number of times the item has been ordered the most.

If I have items A and B and A has been ordered 5 times and B 3 three times then I would bring back

A
B

My criteria code looks like this:

Criteria criteria = this.getSession().createCriteria(OrderItem.class);
criteria.setProjection(Projections.groupProperty("item.id"));
criteria.setFirstResult(pStartIndex);
criteria.setMaxResults(pRecordCount);
criteria.setResultTransformer(Criteria.ROOT_ENTITY);
criteria.add(Restrictions.isNotNull("status");
*** Here is the problem
criteria.addOrder(Order.desc(Projections.count("item.id").toString()));

Unfortunately Order.desc() takes a String for a propertyName and Projections.count("") returns a CountProjection. I'm sure the *toString()* call is wrong but can't figure a way to do this.

I am surprised that I could not use the CountProjection object to somehow tell what propertyName it is counting and pass that info on to the order by.

Any help would greatly be appreciated even if it is to inform me the Criteria API is unable to do this right now.

Thanks

I see Hibernate believes the propertyName for the Order by is count(item.id) which of course isn't a propertyName.

So I'm still wondering if there is a way to let the order by use the propertyName it was created with to pass into the toType(String propertyName) method of the AbstractPropertyMapping class so a propertyException isn't thrown.

org.hibernate.QueryException: could not resolve property: count(item of: org.mycompany.model.OrderItem
at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
at org.hibernate.persister.entity.AbstractEntityPersister.getSubclassPropertyTableNumber(AbstractEntityPersister.java:1
282)
at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:31)
at org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersister.java:1257)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumns(CriteriaQueryTranslator.java:433)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getColumnsUsingProjection(CriteriaQueryTranslator.java:393)
at org.hibernate.criterion.Order.toSqlString(Order.java:45)
at org.hibernate.loader.criteria.CriteriaQueryTranslator.getOrderBy(CriteriaQueryTranslator.java:347)
at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:71)
at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:67)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1514)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)

I'm still unable to find a solution to this problem using the Criteria API. Therefore I'm currently using HQL.

If anyone knows of a solution using the Criteria API please let me know or if it isn't currently possible with the Criteria API.

Thanks!

bump

I ran across this post while wondering the same thing, and here is the solution.

You need to set your projection up with an alias, then use that alias in the propertyName for the Order.



HTH

I'm still have the same problem viewtopic.php?f=1&t=1015443