Aggregators

Sometimes you don’t want rows from a table but a value to sum it up. Use aggregators in that case.

Root aggregators

  • Registry

  • Query

  • SQL output

[{:key :farmers/total
  :type :root
  :table "farmer"
  :aggregate true
  :formula [:count-*]}]
[:farmers/total]
SELECT COUNT(*) AS `farmers/total`  FROM `farmer`

Join aggregators

Aggregators can be a join, too.

  • Registry

  • Query

  • Data

  • Result

[{:key :person/id
  :type :true-column
  :primary-key true
  :output [:person/pet-count]} (1)
 {:key :person/pet-count
  :join-path [:person/id :person-pet/person-id :person-pet/pet-id :pet/id]
  :aggregate true
  :formula [:count-*]}]
1 Just like normal joins, join aggregators must be mentioned in :output elsewhere. 
[{[:person/id 1]
  [:person/id
   :person/name
   :person/pet-count]}]

Table person:

id name

1

Mary

2

John

Table pet:

id name

10

Tom

20

Jerry

Table person_pet:

person_id pet_id

1

10

1

20

 
{[:person/id 1]
 {:person/id        1
  :person/name      "Mary"
  :person/pet-count 2}}

With filters and pagination

Aggregators can be used together with filters and pagination!

  • Registry

  • Query

  • SQL output

[{:key :farmers/total
  :type :root
  :table "farmer"
  :aggregate true
  :formula [:count-*]}]

Count number of all farmers whose name starting with "a":

`[(:farmers/total {:filter [:like :farmer/name "a%"]})]
Parameterized query:
SELECT COUNT(*) AS `farmers/total` FROM `farmer`
WHERE `farmer`.`name` LIKE ?

parameters:

["a%"]

Common SQL aggregators

:avg, :max, :min, :count, :count-*

Aggregators vs Pseudo-columns

An aggregator is meant to produce a single value, while pseudo columns are for adding one or more columns to all rows.

For instance, if you want the value of:

max (`table_a`.`column_x`)

you will need an aggregator:

;; registry
[{:key :your/aggregator
  ... ;; let it be root or join
  :aggregate true
  :formula [:max :table-a/column-x]}]

In contrast, if you want the value of:

max (`table_a`.`column_x`, `table_a`.`column_y`)

you will need a pseudo-column:

;; registry
{:key :your/other-column
 :type :pseudo-column
 :formula [:max :table-a/column-x :table-a/column-y]}

More freedom with S-expressions

You’re free to use whatever s-expression in your aggregator, as long as the final result returned by SQL is a single value.

;; registry
{:key :your/aggregator
 ... ;; let it be root or join
 :aggregate true
 :formula
 [:/ [:+ [:* 10 [:max :table-a/column-x]]
      [:max :table-a/column-y]]
  2]}