Having | Query

We can add expressions to the having clause when we use Dynamic formula, @Aggregation properties or RawSql.

Dynamic formula

See here for more details on dynamic aggregation formula (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

With @Aggration and @Sum properties these are properties on the query bean so we just use them as normal but after having().

Example:

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

  ...

  LocalDate orderDate;

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

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

So we can use maxOrderDate and totalCount properties like other query bean properties. Add predicates on these aggregation properties to the having clause like:

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

With parsed RawSql we can also add expressions to the having clause.

Example

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();