Hibernate选择groupProperty，rowCount与rowCount> N + [英] Hibernate select groupProperty , rowCount with rowCount > n?

问题描述

对不起，如果这是一个愚蠢的问题，但我坚持了整个下午的这个问题，但无法找到解决方案，因为我不熟练使用复杂的SQL：

我想找到来自msg发送次数>阈值的表的前n个发送消息的用户，这是我的标准：

  Criteria c = session.createCriteria（Message.class）; 
 ProjectionList plist = Projections.projectionList（）; 
 plist.add（Projections.groupProperty（user））; 
 plist.add（Projections.rowCount（），count）; 
 c.setProjection（plist）; 
 c.addOrder（Order.desc（count））; 
 
 c.setFirstResult（0）; 
 c.setMaxResults（count）; 
  

这是我可以编写的内容，但它缺少过滤rowCount低于一些门槛。如何使用标准实现它？非常感谢！

--------------更新---------------- --------

谢谢@TheStijn，我试过了。我现在可以使用子查询来实现我的目标，但生成的查询不是很聪明！查看生成的SQL：

  select 
 this_.fromUser as y0_，
 count（*）as y1_ 
 from 
 Message this_ 
 where 
 this_.fromUser不为空
和this_.created> ;? 
和this_.created <？ 
和？ < =（
从
中选择
 count（*）作为y0_ 
消息msg_ 
其中
 msg_.fromUser = this_.fromUser 
和msg_.fromUser不为空
和msg_.created> ;? 
和msg_.created <？
）
分组由
 this_.fromUser 
命令按
 y1_ desc限制？ 
  

也就是说，子查询重复了大部分主查询，其中I认为这有点多余。是否有任何标准构建这样的SQL查询：

  select 
 this_.fromUser as y0_，
 count （*）as y1_ 
 from 
 Message this_ 
 where 
 this_.fromUser is not null 
 and this_.created> ;? 
和this_.created <？ 
和y1_> ？ // threshold 
 group by 
 this_.fromUser 
 order by 
 y1_ desc limit？ 
  

非常感谢！

似乎使用HQL更容易做到这一点，但我对Criteria方式感到好奇）

你需要一个额外的子查询如：

  DetachedCriteria subQuery = DetachedCriteria.forClass（Message.class，msg）; 
 subQuery.add（Restrictions.eqProperty（msg.user，mainQuerymsg.user））; 
 subQueryEntriesCount.setProjection（Projections.rowCount（））; 
 
 c.add（Subqueries.lt（1L，subQuery））; 
  

mainQuerymsg<你的主要标准，所以你需要创建这些标准的别名 createCriteria（MEssage.class，别名）

Sorry if it is a dumb question but I've stuck with this problem for a whole afternoon but cannot find a solution because I'm not skilled with complicated SQL :

I want to find "Top n message-sending users from a table with msg sent count > threshold" , this is my criteria :

Criteria c = session.createCriteria(Message.class);
ProjectionList plist = Projections.projectionList();
plist.add(Projections.groupProperty("user"));
plist.add(Projections.rowCount() , "count");
c.setProjection(plist);
c.addOrder(Order.desc("count"));

c.setFirstResult(0);
c.setMaxResults(count);

This is what I can write , but it lacks of "filtering rows with rowCount lower than some threshold". How to implement it with criteria ? Thanks a lot !

-------------- updated ------------------------

Thanks @TheStijn , I tried . I now can use subquery to achieve my goal , but the generated query is not so clever ! See the generated SQL :

select
    this_.fromUser as y0_,
    count(*) as y1_ 
from
    Message this_ 
where
    this_.fromUser is not null 
    and this_.created>? 
    and this_.created<? 
    and ? <= (
        select
            count(*) as y0_ 
        from
            Message msg_ 
        where
            msg_.fromUser=this_.fromUser 
            and msg_.fromUser is not null 
            and msg_.created>? 
            and msg_.created<?
    ) 
group by
    this_.fromUser 
order by
    y1_ desc limit ?

That is , the subquery repeats most of the main query , which I think it is a little redundant . Is there any criteria that builds such SQL queries :

select
    this_.fromUser as y0_,
    count(*) as y1_ 
from
    Message this_ 
where
    this_.fromUser is not null 
    and this_.created>? 
    and this_.created<? 
    and y1_ > ? // threshold
group by
    this_.fromUser 
order by
    y1_ desc limit ?

Thanks a lot !

(It seems much easier to use HQL to do this , but I am curious about the Criteria way)

解决方案

You'll need an additional subquery like:

    DetachedCriteria subQuery = DetachedCriteria.forClass(Message.class, "msg");
    subQuery.add(Restrictions.eqProperty("msg.user", "mainQuerymsg.user"));
    subQueryEntriesCount.setProjection(Projections.rowCount());

    c.add(Subqueries.lt(1L, subQuery));

mainQuerymsg < your main criteria so you'ill need to create these criteria with an alias createCriteria(MEssage.class, "alias")

这篇关于Hibernate选择groupProperty，rowCount与rowCount&gt; N +的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！