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

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

[:farmers/total]

SELECT COUNT(*) AS `farmers/total`  FROM `farmer`

With filters and pagination

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

Floor plan
Query
SQL output
{:idents
 {:farmers/total "farmer"}
 :aggregators
 {: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 ?

parameters:

["a%"]

Aggregators against idents vs aggregators against joins

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

Floor-plan
Query
Data
Result
{:idents
 {:person/by-id :person/id}
 :joins
 {: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]}
 :aggregators
 {:person/pet-count [:count-*]}}

[{[:person/by-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/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]]
                                     2]}}

results matching ""

    No results matching ""