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 main Table
.
(
joinedTableId: string,
on: string,
): JoinedAs
Type | Description | |
---|---|---|
joinedTableId | string | |
on | string | The |
returns | JoinedAs | A JoinedAs object so that the joined Table Id can be optionally aliased. |
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 main Table
.
(
joinedTableId: string,
on: (getCell: GetCell, rowId: string) => undefined | string,
): JoinedAs
Type | Description | |
---|---|---|
joinedTableId | string | |
on | (getCell: GetCell, rowId: string) => undefined | string | A callback that takes a |
returns | JoinedAs | A JoinedAs object so that the joined Table Id can be optionally aliased. |
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
Type | Description | |
---|---|---|
joinedTableId | string | |
fromIntermediateJoinedTableId | string | The |
on | string | The |
returns | JoinedAs | A JoinedAs object so that the joined Table Id can be optionally aliased. |
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, intermediateJoinedTableRowId: string) => undefined | string,
): JoinedAs
Type | Description | |
---|---|---|
joinedTableId | string | |
fromIntermediateJoinedTableId | string | The |
on | (getIntermediateJoinedCell: GetCell, intermediateJoinedTableRowId: string) => undefined | string | A callback that takes a |
returns | JoinedAs | A JoinedAs object so that the joined Table Id can be optionally aliased. |
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 main 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 main Table
's original Row
Ids
.
This example shows a query that joins a single Table
by using an Id
present in the main query Table
.
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 result Table
.
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 main Table
rather than a single Cell
.
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.
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, rowId) =>
`${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'}}
v2.0.0