Chainable in-memory inner and left joins with where and select
A reporting screen keeps a few small tables in memory (customers, orders, line items) and needs SQL-style joins over them: “every order with its customer”, “every customer, with their orders if they have any”, “customers who never ordered”. Build a small chainable join builder for it.
Implement queryFrom(alias, rows). It starts a query over one table and returns a query object.
queryFrom(alias, rows)
.innerJoin(alias, rows, on) // keep only combinations where on(...) is truthy
.leftJoin(alias, rows, on) // like innerJoin, but keep unmatched rows with null
.where(predicate) // filter the joined rows
.select(fields) // pick output fields
.all() // => array of result rows
While a query runs, each joined row is an object with one key per alias, in the order the tables were added. queryFrom('orders', orders) starts with one joined row { orders: order } per order, in input order.
innerJoin(alias, rows, on): for each current joined row (in order), and for each row of the new table (in order), build the combined row { ...joined, [alias]: candidate } and call on(combined). Keep every combination where it is truthy. A joined row with no match is dropped. One joined row can match several candidates.leftJoin(alias, rows, on): the same, except that a joined row with no match is kept once with [alias]: null.on is only ever called with a real candidate under the new alias. Aliases from earlier left joins can be null, so later on functions must handle that themselves.Error.where(predicate) keeps joined rows where predicate(joinedRow) is truthy. It runs after all joins. Several where calls must all pass.select(fields) maps each output key to a path. 'alias.column' reads that column, or gives null when the alias is null. A bare 'alias' gives a copy of that alias’s whole row, or null. Output keys appear in the order of fields. A later select replaces an earlier one.all() runs the query. Without select, each result is a joined row in which every non-null table row is a shallow copy.all returns a new query and leaves the one it was called on unchanged, so a base query can be reused.all() is called, so rows pushed into an input array after the query was built are included.const customers = [
{ id: 'c1', name: 'Ana' },
{ id: 'c2', name: 'Raj' },
{ id: 'c3', name: 'Ivy' },
];
const orders = [
{ id: 'o1', customerId: 'c2', total: 40 },
{ id: 'o2', customerId: 'c1', total: 15 },
{ id: 'o3', customerId: 'c9', total: 99 },
];
queryFrom('orders', orders)
.innerJoin('customers', customers, (r) => r.orders.customerId === r.customers.id)
.select({ order: 'orders.id', buyer: 'customers.name' })
.all();
// => [{ order: 'o1', buyer: 'Raj' }, { order: 'o2', buyer: 'Ana' }] (o3 has no customer)
const everyone = queryFrom('customers', customers)
.leftJoin('orders', orders, (r) => r.orders.customerId === r.customers.id);
everyone.all();
// => [
// { customers: { id: 'c1', name: 'Ana' }, orders: { id: 'o2', customerId: 'c1', total: 15 } },
// { customers: { id: 'c2', name: 'Raj' }, orders: { id: 'o1', customerId: 'c2', total: 40 } },
// { customers: { id: 'c3', name: 'Ivy' }, orders: null },
// ]
everyone.where((r) => r.orders === null).select({ name: 'customers.name' }).all();
// => [{ name: 'Ivy' }] (customers who never ordered)