Order by count of association

toddpi314 · **Posted:** Mon Feb 07, 2011 3:00 am

How would I order by count of a property list?

class Foo
{
@ManyToMany(targetEntity = Bar.class, cascade = CascadeType.ALL)
List<Bar> bars = ArrayList<Bar>();
}

I would like to get a list of Foo's ordered desc by bars.count

Any help would be much appreciated.
Thanks!

toddpi314 · **Posted:** Wed Feb 09, 2011 4:49 pm

robin11 · **Joined:** Fri Jan 07, 2011 7:23 am **Posts:** 19

Use the size function (4.6.17.2.2 Arithmetic Functions):

Code:

select f from Foo order by size(f.bars);

toddpi314 · **Posted:** Fri Feb 18, 2011 12:42 pm

This works great in HQL!

Does anyone know about a Criteria Equivalent?
The queries I am working with are against a deep object graph, and allot of criteria has accumulated that must be re-coded in HQL just to use the Size() function.

Man, criteria solution would sure help...

robin11 · **Joined:** Fri Jan 07, 2011 7:23 am **Posts:** 19

Note that this is standard JPQL syntax (maybe it's HQL too), so the JPA Criteria equivalent would be

Quote:

CriteriaBuilder.size()

pretty easy isn't it?

toddpi314 · **Posted:** Wed Mar 02, 2011 4:37 am

I am still having issue with this topic.
So, here are some more details so perhaps someone can give me literal syntax for my situation.

- Define Class Person and Shoe
- Add Collection to Person type called 'Shoes' that is a one-to-many of type Shoe

Criteria crit = session.createCriteria(Person.class);
crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
crit.setFirstResult(pageNumber * pageSize);
crit.setMaxResults(pageSize);
result <- crit.list();

I would like to return a list that is sorted by the Count of Shoes per person, descending.
For instance,

Shoe Count | Person
5 Todd
4 Roger
1 Biff

Can someone show me an example of how I can achieve this?

toddpi314 · **Posted:** Thu Mar 03, 2011 8:51 pm

Can any hibernate vetrans help with this?

robin11 · **Joined:** Fri Jan 07, 2011 7:23 am **Posts:** 19

Something like:

Code:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Person> criteria = cb.createQuery(Person.class);
Root<Person> person = criteria.from(Person.class);
criteria.select(person).orderBy(cb.desc(cb.size(person.get(Person_.shoes))));
TypedQuery<Person> query  = em.createQuery(criteria);
...