Request criteria: order by quantity

Question

I am trying to fulfill a criteria query that returns the most answered questions in stackoverflow, for example faq.

The question contains several answers.

I am trying to return with the query criterion the most answered questions ordered by the number of answers to the question.

Does anyone know what to use in sleep mode?

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 a list of objects []. Each object [] contains the identifier of the question as the first element and the number of answers to this question as the second element. questions are sorted in descending order of answers.

(: ), groupProperty (: add(Projections.groupProperty("q.text")))

SQL, , :

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;