setQueryDefinition

The setQueryDefinition method lets you set the definition of a query.

setQueryDefinition(
  queryId: string,
  tableId: string,
  query: (keywords: {
    select: Select;
    selectAll: SelectAll;
    join: Join;
    where: Where;
    group: Group;
    having: Having;
    param: Param;
  }) => void,
  paramValues?: ParamValues,
): Queries

Every query definition is identified by a unique Id, and if you re-use an existing Id with this method, the previous definition is overwritten.

A query provides a tabular result formed from each Row within a root Table. The definition must specify this Table (by its Id) to be aggregated. Other Tables can be joined to that using Join clauses.

The third query parameter is a callback that you provide to define the query. That callback is provided with a keywords object that contains the functions you use to define the query, like select, join, and so on. You can see how that is used in the simple example below. The following six clause types are supported:

• The Select type describes a function that lets you specify a Cell or calculated value for including into the query's result.
• The Join type describes a function that lets you specify a Cell or calculated value to join the main query Table to others, by Row Id.
• The Where type describes a function that lets you specify conditions to filter results, based on the underlying Cells of the main or joined Tables.
• The Group type describes a function that lets you specify that the values of a Cell in multiple ResultRows should be aggregated together.
• The Having type describes a function that lets you specify conditions to filter results, based on the grouped Cells resulting from a Group clause.
• The Param type (since v7.2) describes a function that lets you specify parameters for a parameterized query.

Full documentation and examples are provided in the sections for each of those clause types.

Additionally, you can use the getResultSortedRowIds method and addResultSortedRowIdsListener method to sort and paginate the results.

import {createQueries, createStore} from 'tinybase';

const store = createStore().setTable('pets', {
  fido: {species: 'dog', color: 'brown'},
  felix: {species: 'cat', color: 'black'},
  cujo: {species: 'dog', color: 'black'},
});

const queries = createQueries(store);
queries.setQueryDefinition('dogColors', 'pets', ({select, where}) => {
  select('color');
  where('species', 'dog');
});

console.log(queries.getResultTable('dogColors'));
// -> {fido: {color: 'brown'}, cujo: {color: 'black'}}

When called with true as its second parameter, the setQueryDefinition method lets you define a query whose root is the result of another query, rather than an underlying Table.

setQueryDefinition(
  queryId: string,
  asQuery: true,
  rootQueryId: string,
  query: (keywords: {
    select: Select;
    selectAll: SelectAll;
    join: Join;
    where: Where;
    group: Group;
    having: Having;
    param: Param;
  }) => void,
  paramValues?: ParamValues,
): Queries

This allows you to build more complex queries out of simpler ones and pipe them together. The same clause types are supported as when the query is based on a Table.

import {createQueries, createStore} from 'tinybase';

const store = createStore().setTable('pets', {
  fido: {species: 'dog', color: 'brown'},
  felix: {species: 'cat', color: 'black'},
  cujo: {species: 'dog', color: 'black'},
});

const queries = createQueries(store);
queries.setQueryDefinition('dogs', 'pets', ({select, where}) => {
  select('color');
  where('species', 'dog');
});
queries.setQueryDefinition(
  'blackDogs',
  true,
  'dogs',
  ({select, where}) => {
    select((_, rowId) => rowId).as('petId');
    where('color', 'black');
  },
);

console.log(queries.getResultTable('blackDogs'));
// -> {cujo: {petId: 'cujo'}}