TinyBase

Group

The Group type describes a function that lets you specify that the values of a Cell in multiple result Rows should be aggregated together.

(
  selectedCellId: Id,
  aggregate: "count" | "sum" | "avg" | "min" | "max" | Aggregate,
  aggregateAdd?: AggregateAdd,
  aggregateRemove?: AggregateRemove,
  aggregateReplace?: AggregateReplace,
): GroupedAs
TypeDescription
selectedCellIdId

The Id of the Cell to aggregate. If the underlying Cell was selected 'as' a different Id, that should instead be used.

aggregate"count" | "sum" | "avg" | "min" | "max" | Aggregate

Either a string representing one of a set of common aggregation techniques ('count', 'sum', 'avg', 'min', or 'max'), or a function that aggregates Cell values from each Row to create the aggregate's overall.

aggregateAdd?AggregateAdd

A function that can be used to optimize a custom Aggregate by providing a shortcut for when a single value is added to the input values - for example, when a Row is added to the Table.

aggregateRemove?AggregateRemove

A function that can be used to optimize a custom Aggregate by providing a shortcut for when a single value is removed from the input values - for example ,when a Row is removed from the Table.

aggregateReplace?AggregateReplace

A function that can be used to optimize a custom Aggregate by providing a shortcut for when a single value in the input values is replaced with another - for example, when a Row is updated.

returnsGroupedAs

A GroupedAs object so that the grouped Cell Id can be optionally aliased.

The Group function is provided to the third query parameter of the setQueryDefinition method. When called, it should refer to a Cell Id (or aliased Id) specified in one of the Select functions, and indicate how the values should be aggregated.

This is applied after any joins or where-based filtering.

If you provide a Group for every Select, the result will be a single Row with every Cell having been aggregated. If you provide a Group for only one, or some, of the Select clauses, the others will be automatically used as dimensional values (analogous to the 'group bysemantics in SQL), within which the aggregations ofGroup` Cells will be performed.

You can join the same underlying Cell multiple times, but in that case you will need to use the 'as' function to distinguish them from each other.

The second parameter can be one of five predefined aggregates - 'count', 'sum', 'avg', 'min', and 'max' - or a custom function that produces your own aggregation of an array of Cell values.

The final three parameters, aggregateAdd, aggregateRemove, aggregateReplace need only be provided when you are using your own custom aggregate function. These give you the opportunity to reduce your custom function's algorithmic complexity by providing shortcuts that can nudge an aggregation result when a single value is added, removed, or replaced in the input values.

Examples

This example shows a query that calculates the average of all the values in a single selected Cell from a joined Table.

const store = createStore()
  .setTable('pets', {
    fido: {species: 'dog'},
    felix: {species: 'cat'},
    cujo: {species: 'dog'},
    lowly: {species: 'worm'},
  })
  .setTable('species', {
    dog: {price: 5},
    cat: {price: 4},
    worm: {price: 1},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join, group}) => {
  select('species', 'price');
  // from pets
  join('species', 'species');
  group('price', 'avg').as('avgPrice');
});

console.log(queries.getResultTable('query'));
// -> {0: {avgPrice: 3.75}}
// 2 dogs at 5, 1 cat at 4, 1 worm at 1: a total of 15 for 4 pets

This example shows a query that calculates the average of a two Cell values, aggregated by the two other dimensional 'group by' Cells.

const store = createStore()
  .setTable('pets', {
    fido: {species: 'dog', color: 'brown', owner: 'alice'},
    felix: {species: 'cat', color: 'black', owner: 'bob'},
    cujo: {species: 'dog', color: 'black', owner: 'bob'},
    lowly: {species: 'worm', color: 'brown', owner: 'alice'},
    carnaby: {species: 'parrot', color: 'black', owner: 'bob'},
    polly: {species: 'parrot', color: 'red', owner: 'alice'},
  })
  .setTable('species', {
    dog: {price: 5, legs: 4},
    cat: {price: 4, legs: 4},
    parrot: {price: 3, legs: 2},
    worm: {price: 1, legs: 0},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join, group}) => {
  select('pets', 'color'); //    group by
  select('pets', 'owner'); //    group by
  select('species', 'price'); // grouped
  select('species', 'legs'); //  grouped
  // from pets
  join('species', 'species');
  group('price', 'avg').as('avgPrice');
  group('legs', 'sum').as('sumLegs');
});

queries.forEachResultRow('query', (rowId) => {
  console.log({[rowId]: queries.getResultRow('query', rowId)});
});
// -> {0: {color: 'brown', owner: 'alice', avgPrice: 3, sumLegs: 4}}
// -> {1: {color: 'black', owner: 'bob', avgPrice: 4, sumLegs: 10}}
// -> {2: {color: 'red', owner: 'alice', avgPrice: 3, sumLegs: 2}}

This example shows a query that calculates the a custom aggregate of one Cell's values, grouped by another. Note how aggregateAdd, aggregateRemove, and aggregateReplace parameters are provided to make the custom aggregation more efficient as individual values are added, removed, or replaced during the lifecycle of the Table.

const store = createStore()
  .setTable('pets', {
    fido: {species: 'dog', owner: 'alice'},
    felix: {species: 'cat', owner: 'bob'},
    cujo: {species: 'dog', owner: 'bob'},
    lowly: {species: 'worm', owner: 'alice'},
    carnaby: {species: 'parrot', owner: 'bob'},
    polly: {species: 'parrot', owner: 'alice'},
  })
  .setTable('species', {
    dog: {price: 5, legs: 4},
    cat: {price: 4, legs: 4},
    parrot: {price: 3, legs: 2},
    worm: {price: 1, legs: 0},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join, group}) => {
  select('pets', 'owner'); //    group by
  select('species', 'price'); // grouped
  // from pets
  join('species', 'species');
  group(
    'price',
    (cells) => Math.min(...cells.filter((cell) => cell > 2)),
    (current, add) => (add > 2 ? Math.min(current, add) : current),
    (current, remove) => (remove == current ? undefined : current),
    (current, add, remove) =>
      remove == current
        ? undefined
        : add > 2
        ? Math.min(current, add)
        : current,
  ).as('lowestPriceOver2');
});

queries.forEachResultRow('query', (rowId) => {
  console.log({[rowId]: queries.getResultRow('query', rowId)});
});
// -> {0: {owner: 'alice', lowestPriceOver2: 3}}
// -> {1: {owner: 'bob', lowestPriceOver2: 3}}
// Both have a parrot at 3. Alice's worm at 1 is excluded from aggregation.

Since

v2.0.0