Criteria query : order by count

Question

I'm trying to do a criteria query that returns the most answered questions in an stackoverflow like faq.

A question contains multiple answers.

I'm trying to return with a criteria query the most answered questions ordered by number of answers per question.

Any one knows what should I use in the hibernate criteria util ?

Answer 1

Criteria criteria = session.createCriteria(Question.class, "q");
criteria.createAlias("q.answers", "answer", Criteria.LEFT_JOIN);
criteria.setProjection(Projections.projectionList().add(Projections.groupProperty("q.id"))
                                                   .add(Projections.count("answer.id").as("numberOfAnswers")));
criteria.addOrder(Order.desc("numberOfAnswers"));

This will return you a list of Object[]. Each Object[] contains the ID of the question as first element, and the number of answers of this question as second element. the questions are sorted by descending number of answers.

If you needs additional properties (example : question text), then add additional groupProperty projections (example : add(Projections.groupProperty("q.text")))

The SQL corresponding to this criteria query looks like this :

select this_.ID_QUESTION as y0_, count(answer1_.ID_ANSWER) as y1_ from QUESTION this_ left outer join ANSWER answer1_ on this_.ID_QUESTION=answer1_.ID_QUESTION group by this_.ID_QUESTION order by y1_ desc;