JSDoc: Tutorial: 009 - Select

In the last example we examined a query that involved some JOINs using the From#join method. This method will accept either a string argument or an object. When a string is passed, an "implicit" join is performed when possible. An implicit join is one such that node-data-mapper attempts to automatically derive the JOIN predicate and relationship type. This is only possible when there is exactly one relationship between the joined tables.

Let's examine one of the joins from the previous example more closely, and then look at explicit joins.

dataContext
  .from('staff s')
  .leftOuterJoin('s.bonuses b');

First, we select from the staff table, giving it an alias of s. Then, a From#leftOuterJoin is performed on the bonuses table. When a string is supplied as an argument (e.g. "s.bonuses b") it must follow the format specified by the From#parseJoinString method, which is shown here for convenience.

[<parent-table-alias>.]<table-name>[ [as ]<table-alias>]

As mentioned above, however, implicit joins are not always possible (nor desirable). It's therefore possible to pass the From#join method a TableMetaList~TableMeta object instead of a string. In the code snippet above, the string passed to the From#parseJoinString method (i.e. "s.bonuses b") will be parsed and converted to a TableMetaList~TableMeta object. The above "implicit" join is equivalent to this "explicit" one.

dataContext
  .from('staff s')
  .leftOuterJoin({
    parent:  's',
    table:   'bonuses',
    as:      'b',
    relType: 'many',
    on:      {$eq: {'s.staffID': 'b.staffID'}}
  });

Let's take a closer look at each property.

The parent of the bonuses table is set to s, which is the alias for the staff table. When the query result is serialized to a normalized JavaScript object, an array of bonuses will be nested under each staff member (bonuses is a child of staff).
The joined-in table is bonuses, with an alias of b.
relType--which is an abbreviation for relationship type--is set to "many", because each staff member can have many bonuses.
The tables are joined together using the staffID column.

It's worth pointing out that the relType property can be set to "many" or "single." As can be seen in the example below, when joining from bonuses to staff, the relType property should be set to "single." This is because each bonus record is tied to exactly one staff record, so it's preferable to have bonuses serialized to an object rather than an array.

Also worth mentioning: The on property takes a Condition object, and any valid Condition object can be provided as a join predicate.

Here is a more complex join example, using an explicit join from bonuses to staff.

'use strict';

const MySQLDriver = require('node-data-mapper-mysql').MySQLDriver;
const driver      = new MySQLDriver(require('../bikeShopConOpts.json'));
const util        = require('util');

driver
  .initialize()
  .then(runQuery)
  .then(printResult)
  .catch(console.error)
  .finally(() => driver.end());

function runQuery(dataContext) {
  // Get all the bonuses in the system, with the single staff member for each
  // bonus, but only for staff members over the age of 21.
  const query = dataContext
    .from('bonuses b')
    .innerJoin(
      {
        parent:  'b',
        table:   'staff',
        as:      's',
        relType: 'single',
        on: {
          $and: [
            {$eq:  {'s.staffID': 'b.staffID'}},
            {$gte: {'s.age': ':minAge'}}
          ]
        }
      },
      {minAge: 22}
    )
    .select(
      's.staffID', 's.lastName', 's.age',
      'b.bonusID', 'b.amount'
    );

  console.log('Query:');
  console.log(query.toString(), '\n');

  return query
    .execute();
}

function printResult(result) {
  console.log('Result:');
  console.log(util.inspect(result, {depth: null}));
}

The output of this example is printed below ($ node example/retrieve/explicitJoin.js):

Query:
SELECT  `s`.`staffID` AS `s.staffID`,
        `s`.`lastName` AS `s.lastName`,
        `s`.`age` AS `s.age`,
        `b`.`bonusID` AS `b.bonusID`,
        `b`.`amount` AS `b.amount`
FROM    `bonuses` AS `b`
INNER JOIN `staff` AS `s` ON (`s`.`staffID` = `b`.`staffID` AND `s`.`age` >= :minAge) 

Result:
{ bonuses: 
   [ { bonusID: 2,
       amount: 600,
       staff: { staffID: 6, lastName: 'Stocking', age: 29 } },
     { bonusID: 3,
       amount: 320,
       staff: { staffID: 8, lastName: 'Xavier', age: 22 } } ] }

Tutorial: 009 - Select - Joins

009 - Select - Joins