Having

当我们使用动态公式、@Aggregation 属性或 RawSql 时，可以向having 子句添加表达式。

 

动态公式

请参阅 此处 了解有关动态聚合公式（sum、min、max、count、avg）的更多详细信息。 

// e.g. sum(hours)

List<DMachineStats> result =
  new QDMachineStats()
  .select("date, sum(totalKms), sum(hours)")
  .havingClause()
    .gt("sum(hours)", 2)                         // (1) having clause
  .findList();

select t0.date, sum(t0.total_kms), sum(t0.hours)
from d_machine_stats t0
group by t0.date
having sum(t0.hours) > ?                        -- (1) having clause

 

@Aggration

使用@Aggration 和 @Sum 属性，这些属性是查询 Bean 上的属性，因此我们只需像往常一样使用它们，但位于 having() 之后。

示例
@Entity
@Table(name = "orders")
public class Order extends BaseModel {

  ...

  LocalDate orderDate;

  @Aggregation("max(orderDate)")     // aggregation property
  LocalDate maxOrderDate;

  @Aggregation("count(*)")           // aggregation property
  Long totalCount;

因此，我们可以像其他查询 Bean 属性一样使用 maxOrderDate 和 totalCount 属性。向 having 子句中添加针对这些聚合属性的谓词，如下所示 

List<Order> orders = new QOrder()
  .select(o.status, o.maxOrderDate, o.totalCount)
  .status.notEqualTo(Order.Status.COMPLETE)             // (1) where clause - non aggregate properties
  .having()
  .totalCount.greaterThan(1)                            // (2) having clause - aggregate properties
  .findList();

select t0.status, max(t0.order_date), count(*)
from orders t0
where t0.status <> ?                                    // (1)
group by t0.status
having count(*) > ?                                     // (2)

 

RawSql

使用已解析的 RawSql，我们还可以向 having 子句添加表达式。

示例
String sql =
  "select order_id, count(*) as totalItems, sum(order_qty*unit_price) as totalAmount \n" +
    "from o_order_detail \n" +
    "group by order_id";

RawSql rawSql = RawSqlBuilder.parse(sql).columnMapping("order_id", "order.id").create();

List<OrderAggregate> l2 = DB.find(OrderAggregate.class)
  .setRawSql(rawSql)
  .where()
    .gt("order.id", 0)
  .having()                                           // having clause
    .lt("totalItems", 3)
    .gt("totalAmount", 50)
  .findList();