Sometimes you don't want columns of a table but the total number of records in it. Use :aggregators in that case.

Floor plan
SQL output
 {:farmers/total "farmer"}
 {:farmers/total [:count-*]}}
SELECT COUNT(*) AS `farmers/total`  FROM `farmer`

With filters and pagination

Aggregators work well with filters (and pagination, too!)

Floor plan
SQL output
 {:farmers/total "farmer"}
 {:farmers/total [:count-*]}}

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

`[(:farmers/total {:filters [:like :farmer/name "a%"]})]

Parameterized query:

SELECT COUNT(*) AS `farmers/total` FROM `farmer`
WHERE `farmer`.`name` LIKE ?



Aggregators against idents vs aggregators against joins

In the above examples, aggregators are used against idents. How about aggregators with joins?

 {:person/by-id :person/id}
 {:person/pet-count ;; the join path you would use with a normal join:
  [:person/id :person-pet/person-id :person-pet/pet-id :pet/id]}
 {:person/pet-count [:count-*]}}
[{[:person/by-id 1]

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/by-id 1]
 {:person/id        1
  :person/name      "Mary"
  :person/pet-count 2}}

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:

;; floor-plan
{:aggregators {:your/aggregator [: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:

;; floor-plan
{:pseudo-columns {:your/other-column [: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.

;; floor-plan
{:aggregators {:your/aggregator [:/ [:+ [:* 10 [:max :table-a/column-x]]
                                        [:max :table-a/column-y]]

results matching ""

    No results matching ""