Tutorial: 014 - Delete - Delete Using a From Instance

The previous examples covered deleting models by ID, which reflects a common use case. But sometimes a more complex delete is needed, for example using a WHERE clause or JOINs. Recall that back in the Select all from a Single Table tutorial it was mentioned that the DataContext#from method returns a FromAdapter instance, and that a FromAdapter can be used for deleting and updating as well as selecting. Let's revisit this and see how to perform complex deletes.

'use strict';

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

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

function runQuery(dataContext) {
  const query = dataContext
    .from('staff s')
    .leftOuterJoin('s.bonuses b')
    .where(
      // Staff members that have never received a bonus and are over the age
      // of 65 are getting fired today.
      {
        $and: [
          {$is: {'b.bonusID': null}},
          {$gt: {'s.age': ':overTheHill'}}
        ]
      },
      {overTheHill: 65}
    )
    .delete();

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

  return query
    .execute();
}

function printResult(result) {
  console.log('Result:');
  console.log(result);
}
  

In the above example, staff and bonuses are joined together. Then the query is filtered such that only staff members that have never received a bonus and are over the age of 65 are included. These staff members are deleted.

It's worth pointing out that the FromAdapter#delete method takes an optional tableAlias parameter. If not supplied, then the root table's alias is deleted from (s, the alias of staff, in this case). But, like raw SQL, one can join in a table and then delete from it (e.g. delete('b') would instead delete bonus records).

The example shown above prints the following output when run ($ node example/delete/complexDelete.js):

Query:
DELETE  `s`
FROM    `staff` AS `s`
LEFT OUTER JOIN `bonuses` AS `b` ON `s`.`staffID` = `b`.`staffID`
WHERE   (`b`.`bonusID` IS NULL AND `s`.`age` > :overTheHill) 

Result:
{ affectedRows: 1 }