saferis · v0.20.0

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("aggregate_event_rows_ref_1"."sequenceNr") from "aggregate_event_rows" as "aggregate_event_rows_ref_1" where "aggregate_event_rows_ref_1"."instanceId" = $1
Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(_.amount)(_.min)
  .build
  .sql
select min("aggregate_event_rows_ref_1"."amount") from "aggregate_event_rows" as "aggregate_event_rows_ref_1" where "aggregate_event_rows_ref_1"."instanceId" = $1
Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(_.amount)(_.sum)
  .build
  .sql
select sum("aggregate_event_rows_ref_1"."amount") from "aggregate_event_rows" as "aggregate_event_rows_ref_1" where "aggregate_event_rows_ref_1"."instanceId" = $1
Query[EventRow]
  .where(_.instanceId)
  .eq("instance-1")
  .selectAggregate(_.sequenceNr)(_.count)
  .build
  .sql
select count("aggregate_event_rows_ref_1"."sequenceNr") from "aggregate_event_rows" as "aggregate_event_rows_ref_1" where "aggregate_event_rows_ref_1"."instanceId" = $1
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" = $1

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("aggregate_event_rows_ref_1"."sequenceNr"), $1) from "aggregate_event_rows" as "aggregate_event_rows_ref_1" where "aggregate_event_rows_ref_1"."instanceId" = $2
Query[EventRow]
  .where(_.instanceId)
  .eq("nonexistent")
  .selectAggregate(_.amount)(_.sum.coalesce(BigDecimal(0)))
  .build
  .sql
select coalesce(sum("aggregate_event_rows_ref_1"."amount"), $1) from "aggregate_event_rows" as "aggregate_event_rows_ref_1" where "aggregate_event_rows_ref_1"."instanceId" = $2

Executing Aggregate Queries

Use queryValue[T] to get the aggregate result:

(
  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
  .provideLayer(DocsTransactor.layer)
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