TinyBase logoTinyBase

Where

The Where type describes a function that lets you specify conditions to filter results, based on the underlying Cells of the root or joined Tables.

Calling this function with two parameters is used to include only those Rows for which a specified Cell in the query's root Table has a specified value.

(
  cellId: Id,
  equals: Cell,
): void
TypeDescription
cellIdId

The Id of the Cell in the query's root Table to test.

equalsCell

The value that the Cell has to have for the Row to be included in the result.

returnsvoid

This has no return value.


Calling this function with three parameters is used to include only those Rows for which a specified Cell in a joined Table has a specified value.

(
  joinedTableId: Id,
  joinedCellId: Id,
  equals: Cell,
): void
TypeDescription
joinedTableIdId

The Id of the joined Table to test a value in. If the underlying Table was joined 'as' a different Id, that should instead be used.

joinedCellIdId

The Id of the Cell in the joined Table to test.

equalsCell

The value that the Cell has to have for the Row to be included in the result.

returnsvoid

This has no return value.


Calling this function with one callback parameter is used to include only those Rows which meet a calculated boolean condition, based on values in the main and (optionally) joined Tables.

(condition: (getTableCell: GetTableCell) => boolean): void
TypeDescription
condition(getTableCell: GetTableCell) => boolean

A callback that takes a GetTableCell function and that should return true for the Row to be included in the result.

returnsvoid

This has no return value.

The Where function is provided to the third query parameter of the setQueryDefinition method.

If you do not specify a Where clause, you should expect every non-empty Row of the root Table to appear in the query's results.

A Where condition has to be true for a Row to be included in the results. Each Where class is additive, as though combined with a logical 'and'. If you wish to create an 'or' expression, use the single parameter version of the type that allows arbitrary programmatic conditions.

The Where keyword differs from the Having keyword in that the former describes conditions that should be met by underlying Cell values (whether selected or not), and the latter describes conditions based on calculated and aggregated values - after Group clauses have been applied.

Examples

This example shows a query that filters the results from a single Table by comparing an underlying Cell from it with a value.

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

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

queries.forEachResultRow('query', (rowId) => {
  console.log({[rowId]: queries.getResultRow('query', rowId)});
});
// -> {fido: {species: 'dog'}}
// -> {cujo: {species: 'dog'}}

This example shows a query that filters the results of a query by comparing an underlying Cell from a joined Table with a value. Note that the joined table has also been aliased, and so its alias is used in the Where clause.

const store = createStore()
  .setTable('pets', {
    fido: {species: 'dog', ownerId: '1'},
    felix: {species: 'cat', ownerId: '2'},
    cujo: {species: 'dog', ownerId: '3'},
  })
  .setTable('owners', {
    '1': {name: 'Alice', state: 'CA'},
    '2': {name: 'Bob', state: 'CA'},
    '3': {name: 'Carol', state: 'WA'},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join, where}) => {
  select('species');
  // from pets
  join('owners', 'ownerId').as('petOwners');
  where('petOwners', 'state', 'CA');
});

queries.forEachResultRow('query', (rowId) => {
  console.log({[rowId]: queries.getResultRow('query', rowId)});
});
// -> {fido: {species: 'dog'}}
// -> {felix: {species: 'cat'}}

This example shows a query that filters the results of a query with a condition that is calculated from underlying Cell values from the main and joined Table. Note that the joined table has also been aliased, and so its alias is used in the Where clause.

const store = createStore()
  .setTable('pets', {
    fido: {species: 'dog', ownerId: '1'},
    felix: {species: 'cat', ownerId: '2'},
    cujo: {species: 'dog', ownerId: '3'},
  })
  .setTable('owners', {
    '1': {name: 'Alice', state: 'CA'},
    '2': {name: 'Bob', state: 'CA'},
    '3': {name: 'Carol', state: 'WA'},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join, where}) => {
  select('species');
  select('petOwners', 'state');
  // from pets
  join('owners', 'ownerId').as('petOwners');
  where(
    (getTableCell) =>
      getTableCell('pets', 'species') === 'cat' ||
      getTableCell('petOwners', 'state') === 'WA',
  );
});

queries.forEachResultRow('query', (rowId) => {
  console.log({[rowId]: queries.getResultRow('query', rowId)});
});
// -> {felix: {species: 'cat', state: 'CA'}}
// -> {cujo: {species: 'dog', state: 'WA'}}

Since

v2.0.0