saferis · v0.19.1

Aggregate Functions

Saferis provides type-safe aggregate functions with the selectAggregate method.

Basic Aggregates

Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(_.sequenceNr)(_.max)
  .build
  .sql
select max(sequenceNr) from aggregate_event_rows as aggregate_event_rows_ref_1 where aggregate_event_rows_ref_1.instanceId = ?
Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(_.amount)(_.min)
  .build
  .sql
select min(amount) from aggregate_event_rows as aggregate_event_rows_ref_1 where aggregate_event_rows_ref_1.instanceId = ?
Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(_.amount)(_.sum)
  .build
  .sql
select sum(amount) from aggregate_event_rows as aggregate_event_rows_ref_1 where aggregate_event_rows_ref_1.instanceId = ?
Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(_.sequenceNr)(_.count)
  .build
  .sql
select count(sequenceNr) from aggregate_event_rows as aggregate_event_rows_ref_1 where aggregate_event_rows_ref_1.instanceId = ?
Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(countAll)
  .build
  .sql
select count(*) from aggregate_event_rows as aggregate_event_rows_ref_1 where aggregate_event_rows_ref_1.instanceId = ?

COALESCE for Default Values

Handle NULL results from aggregates with coalesce:

Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(_.sequenceNr)(_.max.coalesce(0L))
  .build
  .sql
select coalesce(max(sequenceNr), ?) from aggregate_event_rows as aggregate_event_rows_ref_1 where aggregate_event_rows_ref_1.instanceId = ?
Query[EventRow]
  .where(_.instanceId)
  .eq("nonexistent")
  .selectAggregate(_.amount)(_.sum.coalesce(BigDecimal(0)))
  .build
  .sql
select coalesce(sum(amount), ?) from aggregate_event_rows as aggregate_event_rows_ref_1 where aggregate_event_rows_ref_1.instanceId = ?

Executing Aggregate Queries

Use queryValue[T] to get the aggregate result:

xa.run(
  for
    _      <- ddl.createTable[EventRow](ifNotExists = true)
    _      <- dml.insert(EventRow(-1, "test", 1L, BigDecimal(100)))
    _      <- dml.insert(EventRow(-1, "test", 5L, BigDecimal(200)))
    _      <- dml.insert(EventRow(-1, "test", 3L, BigDecimal(150)))
    maxSeq <- Query[EventRow]
      .where(_.instanceId)
      .eq("test")
      .selectAggregate(_.sequenceNr)(_.max.coalesce(0L))
      .queryValue[Long]
    total <- Query[EventRow]
      .where(_.instanceId)
      .eq("test")
      .selectAggregate(_.amount)(_.sum)
      .queryValue[BigDecimal]
    count <- Query[EventRow]
      .where(_.instanceId)
      .eq("test")
      .selectAggregate(countAll)
      .queryValue[Long]
  yield (maxSeq, total, count)
).either
Right((Some(5),Some(450),Some(3)))

Available Aggregate Functions

FunctionDescription
_.maxMaximum value
_.minMinimum value
_.sumSum of values
_.countCount of non-null values
_.avgAverage value
countAllCount all rows (COUNT(*))
.coalesce(default)Return default if NULL