TinyBase logoTinyBase

Join

The Join type describes a function that lets you specify a Cell or calculated value to join the main query Table to other Tables, by their Row Id.

Calling this function with two Id parameters will indicate that the join to a Row in an adjacent Table is made by finding its Id in a Cell of the query's root Table.

(
  joinedTableId: string,
  on: string,
): JoinedAs
TypeDescription
joinedTableIdstring

The Id of the Table to join to.

onstring

The Id of the Cell in the root Table that contains the joined Table's Row Id.

returnsJoinedAs

A JoinedAs object so that the joined Table Id can be optionally aliased.

Since

v2.0.0

Calling this function with two parameters (where the second is a function) will indicate that the join to a Row in an adjacent Table is made by calculating its Id from the Cells and the Row Id of the query's root Table.

(
  joinedTableId: string,
  on: (getCell: GetCell, rowId: string) => undefined | string,
): JoinedAs
TypeDescription
joinedTableIdstring

The Id of the Table to join to.

on(getCell: GetCell, rowId: string) => undefined | string

A callback that takes a GetCell function and the root Table's Row Id. These can be used to programmatically calculate the joined Table's Row Id.

returnsJoinedAs

A JoinedAs object so that the joined Table Id can be optionally aliased.

Since

v2.0.0

Calling this function with three Id parameters will indicate that the join to a Row in distant Table is made by finding its Id in a Cell of an intermediately joined Table.

(
  joinedTableId: string,
  fromIntermediateJoinedTableId: string,
  on: string,
): JoinedAs
TypeDescription
joinedTableIdstring

The Id of the distant Table to join to.

fromIntermediateJoinedTableIdstring

The Id of an intermediate Table (which should have been in turn joined to the main query table via other Join clauses).

onstring

The Id of the Cell in the intermediate Table that contains the joined Table's Row Id.

returnsJoinedAs

A JoinedAs object so that the joined Table Id can be optionally aliased.

Since

v2.0.0

Calling this function with three parameters (where the third is a function) will indicate that the join to a Row in distant Table is made by calculating its Id from the Cells and the Row Id of an intermediately joined Table.

(
  joinedTableId: string,
  fromIntermediateJoinedTableId: string,
  on: (getIntermediateJoinedCell: GetCell, intermediateJoinedRowId: string) => undefined | string,
): JoinedAs
TypeDescription
joinedTableIdstring

The Id of the Table to join to.

fromIntermediateJoinedTableIdstring

The Id of an intermediate Table (which should have been in turn joined to the main query table via other Join clauses).

on(getIntermediateJoinedCell: GetCell, intermediateJoinedRowId: string) => undefined | string

A callback that takes a GetCell function and the intermediate Table's Row Id. These can be used to programmatically calculate the joined Table's Row Id.

returnsJoinedAs

A JoinedAs object so that the joined Table Id can be optionally aliased.

Since

v2.0.0

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

You can join zero, one, or many Tables. You can join the same underlying Table multiple times, but in that case you will need to use the 'as' function to distinguish them from each other.

By default, each join is made from the main query Table to the joined table, but it is also possible to connect via an intermediate join Table to a more distant join Table.

Because a Join clause is used to identify which unique Row Id of the joined Table will be joined to each Row of the root Table, queries follow the 'left join' semantics you may be familiar with from SQL. This means that an unfiltered query will only ever return the same number of Rows as the main Table being queried, and indeed the resulting table (assuming it has not been aggregated) will even preserve the root Table's original Row Ids.

Examples

This example shows a query that joins a single Table by using an Id present in the main query Table.

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'},
    '2': {name: 'Bob'},
    '3': {name: 'Carol'},
  });

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

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

This example shows a query that joins the same underlying Table twice, and aliases them (and the selected Cell Ids). Note the left-join semantics: Felix the cat was bought, but the seller was unknown. The record still exists in the ResultTable.

import {createQueries, createStore} from 'tinybase';

const store = createStore()
  .setTable('pets', {
    fido: {species: 'dog', buyerId: '1', sellerId: '2'},
    felix: {species: 'cat', buyerId: '2'},
    cujo: {species: 'dog', buyerId: '3', sellerId: '1'},
  })
  .setTable('humans', {
    '1': {name: 'Alice'},
    '2': {name: 'Bob'},
    '3': {name: 'Carol'},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join}) => {
  select('buyers', 'name').as('buyer');
  select('sellers', 'name').as('seller');
  // from pets
  join('humans', 'buyerId').as('buyers');
  join('humans', 'sellerId').as('sellers');
});

queries.forEachResultRow('query', (rowId) => {
  console.log({[rowId]: queries.getResultRow('query', rowId)});
});
// -> {fido: {buyer: 'Alice', seller: 'Bob'}}
// -> {felix: {buyer: 'Bob'}}
// -> {cujo: {buyer: 'Carol', seller: 'Alice'}}

This example shows a query that calculates the Id of the joined Table based from multiple values in the root Table rather than a single Cell.

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'},
  })
  .setTable('colorSpecies', {
    'brown-dog': {price: 6},
    'black-dog': {price: 5},
    'brown-cat': {price: 4},
    'black-cat': {price: 3},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join}) => {
  select('colorSpecies', 'price');
  // from pets
  join(
    'colorSpecies',
    (getCell) => `${getCell('color')}-${getCell('species')}`,
  );
});

queries.forEachResultRow('query', (rowId) => {
  console.log({[rowId]: queries.getResultRow('query', rowId)});
});
// -> {fido: {price: 6}}
// -> {felix: {price: 3}}
// -> {cujo: {price: 5}}

This example shows a query that joins two Tables, one through the intermediate other.

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'},
  })
  .setTable('states', {
    CA: {name: 'California'},
    WA: {name: 'Washington'},
  });

const queries = createQueries(store);
queries.setQueryDefinition('query', 'pets', ({select, join}) => {
  select(
    (getTableCell) =>
      `${getTableCell('species')} in ${getTableCell('states', 'name')}`,
  ).as('description');
  // from pets
  join('owners', 'ownerId');
  join('states', 'owners', 'state');
});

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

Since

v2.0.0