`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: string,
  equals: Cell,
): void

	Type	Description
`cellId`	`string`	The `Id` of the `Cell` in the query's root `Table` to test.
`equals`	`Cell`	The value that the `Cell` has to have for the `Row` to be included in the result.
returns	`void`	This has no return value.

Since

v2.0.0

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: string,
  joinedCellId: string,
  equals: Cell,
): void

	Type	Description
`joinedTableId`	`string`	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.
`joinedCellId`	`string`	The `Id` of the `Cell` in the joined `Table` to test.
`equals`	`Cell`	The value that the `Cell` has to have for the `Row` to be included in the result.
returns	`void`	This has no return value.

Since

v2.0.0

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

	Type	Description
`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.
returns	`void`	This has no return value.

Since

v2.0.0

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.

import {createQueries, createStore} from 'tinybase';

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.

import {createQueries, createStore} from 'tinybase';

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.

import {createQueries, createStore} from 'tinybase';

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