Batch Deleting

Previously we covered deleting a single model by ID. We can also perform more complex, batch deletes using a where clause and optionally joining tables.

Batch deleting is nearly identical to Batch Updating, so you should be familiar with that section. Like a batch update, a batch delete starts out with a call to DataContext.from. That returns a FromAdapter instance, which can be used to add a where clause, and/or join in additional tables.

FromAdapter.where: Add a where clause to the query.
FromAdapter.innerJoin: Inner join a table.
FromAdapter.leftOuterJoin: Left outer join a table.

The FromAdapter class also has a delete method. It returns a Delete instance that’s used to batch delete records.

Next up, we’ll go through some example code that deletes using a where clause and a join.

Batch Delete a Person’s Phone Numbers

In the example database we defined people and phone_numbers tables along with models for each. Now let’s say we want to delete all the PhoneNumber records associated with a Person, and we want to identify the person by firstName and lastName. We’ll delete from the phone_numbers table, join in people, and set an appropriate where clause. If you’re not familiar with where conditions or joining, take a look at those sections: Conditions; Joining.

// Used for building the delete's where clause.
const cb = new ConditionBuilder();

// Delete all PhoneNumber (pn) records for the Person (p) named
// "Rand al'Thor."
const query: Delete = dataContext
  .from(PhoneNumber, 'pn')
  .innerJoin(Person, 'p', 'pn.person')
  .where(
    cb.and(
      cb.eq('p.firstName', ':fname', 'Rand'),
      cb.eq('p.lastName', ':lname', 'al\'Thor')))
  .delete('pn');

As mentioned above, a Delete instance is returned. It’s a type of Query, so we can view the generated SQL by calling toString.

console.log(query.toString());

To run the query, we execute it.

const result: MutateResultType = await query.execute();

The result will have at least an affectedRows property, indicating the number of deleted rows.

Full Example

The full code presented above can be found in the formn-example repository under src/delete/delete-phone-numbers-for-person.ts.

import { inspect } from 'util';

import {
  MySQLDataContext, ConnectionOptions, ConditionBuilder, Delete,
  MutateResultType
} from 'formn';

import { PhoneNumber } from '../entity/phone-number.entity';
import { Person } from '../entity/person.entity';

async function main() {
  const connOpts: ConnectionOptions = require('../../connections.json');
  const dataContext = new MySQLDataContext();

  try {
    await dataContext.connect(connOpts);

    // Used for building the delete's where clause.
    const cb = new ConditionBuilder();

    // Delete all PhoneNumber (pn) records for the Person (p) named
    // "Rand al'Thor."
    const query: Delete = dataContext
      .from(PhoneNumber, 'pn')
      .innerJoin(Person, 'p', 'pn.person')
      .where(
        cb.and(
          cb.eq('p.firstName', ':fname', 'Rand'),
          cb.eq('p.lastName', ':lname', 'al\'Thor')))
      .delete('pn');

    console.log(query.toString());

    // The result will have at least an affectedRows property.
    const result: MutateResultType = await query.execute();

    console.log(inspect(result, {depth: null, compact: false}));

    await dataContext.end();
  }
  catch(err) {
    console.error(err);
  }
}

main();

Run the example with ts-node.

npx ts-node ./src/delete/delete-phone-numbers-for-person.ts

Output will look like this.

DELETE  `pn`
FROM    `phone_numbers` AS `pn`
INNER JOIN `people` AS `p` ON `pn`.`personID` = `p`.`personID`
WHERE   (`p`.`firstName` = :fname AND `p`.`lastName` = :lname)
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  info: '',
  serverStatus: 34,
  warningStatus: 0
}