标准查询：按次数排序 [英] Criteria query : order by count

问题描述

我试图做一个标准查询，返回像常见问题一样的最常见的问题。

一个问题包含多个答案。 b
$ b

我试图用标准查询返回按照每个问题的答案数排序的答案最多的问题。

任何人都知道我应该在Hibernate标准util中使用什么？

解决方案

  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））; 
  

这会返回一个Object []的列表。每个Object []包含问题的ID作为第一个元素，以及这个问题的答案的数量作为第二个元素。如果你需要额外的属性（例如：问题文本），然后添加额外的groupProperty预测（例如： add（Projections.groupProperty（q.text）））

与此条件查询相对应的SQL如下所示： p>

  select this_.ID_QUESTION as y0_，count（answer1_.ID_ANSWER）as y1_ from QUESTION this_ left外连接ANSWER answer1_ on this_.ID_QUESTION =通过this_.ID_QUESTION按y1_ desc排序的answer1_.ID_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 ?

解决方案

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;

这篇关于标准查询：按次数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！