Best way to destroy a model and related models/collections?

[adriano-di-giovanni]

I can't figure out how to destroy a model and related models/collections.
What's the best way?

[johanneslumpe]

You could do it like this:

SomeModel.forge({id: 2}).fetch({
    withRelated:['someRelation']
}).then(function (item) {
  return item.related('someRelation').invokeThen('destroy').then(function () {
    return item.destroy().then(function () {
      console.log('destroyed!');
    });
  });
});

You should wrap that in a transaction though. This will issue a DELETE query for each item in the collection. For performance it might be better to use a knex query, which bulk deletes all relations from the related table and to then reset the collection on the model.

Update:

Using knex it could look like this:

SomeModel.forge({id: 123})
.fetch()
.then(function (item) {
  var relation = item.someRelation();
  var tableName = relation.relatedData.targetTableName;
  var foreignKey = relation.relatedData.key('foreignKey');

  return Bookshelf.DB.knex(tableName)
  .where(foreignKey, item.id)
  .del()
  .then(function (numRows) {
    console.log(numRows + ' rows have been deleted');
  }).catch(function (err) {
    console.log(err);
  });
});

The above example is for a hasMany or hasOne relation, but you get the idea :)

Using the first method you should get the appropriate destroying and destroyed events for each model, the second method would be kind of a "silent" delete.

[adriano-di-giovanni]

Thanks @johanneslumpe,
do you mean a knex query without using any Bookshelf model or collection?

[johanneslumpe]

Yeah that's what i meant. On your Bookshelf instance a reference to knex is stored. So you could use it to perform this kind of bulk-delete. You would still be using a model, as in my example, so you could retrieve tablenames and keynames easily.

I think it would be nice to have a del method on a relation though, which would perform this kind of bulk delete. Maybe I'll have time to wrap something up.

[johanneslumpe]

@adriano-di-giovanni I just checked something and you can get the same result by doing the following:

var relation = item.someRelation();
var tableName = relation.relatedData.targetTableName;
var foreignKey = relation.relatedData.key('foreignKey');

return relation.sync().query.where(foreignKey, item.id).del().then(function (numRows) {
  console.log(numRows + ' rows have been deleted');
}).catch(function (err) {
  console.log(err);
});

That way you do not have to reference the knex object on your DB instance directly, but can just work with the relation! The above code is meant to be wrapped inside a then function after a fetch of a single model.

[adriano-di-giovanni]

Thanks again, @johanneslumpe

[tgriesser]

Yeah I think there should be a model.relation().destroy()...even if the relation is a collection. I'll look into it.

[johanneslumpe]

@tgriesser It would be able to automatically decide whether to delete child-objects (those who cannot exist without a parent, like children of a hasOne or hasMany relationship) or to just delete the entry from the relation table, based on the type of the relation, right?

[tkellen]

I'm accomplishing this by putting a list of dependents on each model's constructor and recursively descending them to build delete queries.

Sample Models:

var Word = BaseModel.extend({});

var Page = BaseModel.extend({
  words: function () {
    return this.hasMany(Word, 'page_id');
  }
}, { dependents: ['words'] });

var Chapter = BaseModel.extend({
  pages: function () {
    return this.hasMany(Page, 'chapter_id');
  }
}, { dependents: ['pages'] });

var Book = BaseModel.extend({
  chapters: function () {
    return this.hasMany(Chapter, 'book_id');
  }
}, { dependents: ['chapters'] });

Calling cascadeDestroy on a book instance would run the following:

DELETE FROM word WHERE page_id IN (SELECT id FROM page WHERE chapter_id IN (SELECT id FROM chapter WHERE book_id IN (1)));
DELETE FROM page WHERE chapter_id IN (SELECT id FROM chapter WHERE book_id IN (1));
DELETE FROM chapter WHERE book_id IN (1);
DELETE FROM book WHERE id IN (1);

Here is my BaseModel:

var _ = require('lodash');
var sequence = require('when/sequence');
var instanceProps = {
  cascadeDestroy: function () {
    var self = this;
    var queries = this.constructor.cascadeDeletes(this.get('id'));
    var deleteDependents = sequence(queries.map(function (query) {
      return query.del.bind(query);
    }));
    return deleteDependents.then(function () {
      return self.destroy();
    });
  });
};

var classProps = {
  // recursively build a tree of dependent tables
  depMap: function () {
    var map = {};
    var deps = this.dependents;
    deps.forEach(function (dep) {
      var relation = this.prototype[dep]().relatedData;
      map[dep] = {
        model: relation.target,
        key: relation.foreignKey,
        deps: relation.target.depMap()
      }
    }, this);
    return map;
  },
  // build an array of queries that must be executed in order to
  // delete a given model.
  cascadeDeletes: function (parent) {
    var queries = [];
    var deps = this.depMap();
    Object.keys(deps).forEach(function (dep) {
      var query;
      var relation = deps[dep];
      var table = relation.model.prototype.tableName;
      if(_.isNumber(parent)) {
        query = DB.knex(table).column('id').where(relation.key, parent);
      } else {
        query = DB.knex(table).column('id').whereRaw(relation.key+' IN ('+parent.toString()+')');
      }
      queries.push(query);
      queries.push(relation.model.cascadeDeletes(query).reverse());
    }, this);
    return _.flatten(_.compact(queries)).reverse();
  },
};

module.exports = Bookshelf.Model.extend(instanceProps, classProps);

ref: knex/knex#162

[ricardogama]

We just released the bookshelf-cascade-delete plugin which implements the solution proposed by @tkellen, hope it can help you guys!

[crunchtime-ali]

The project leadership of Bookshelf recently changed. In an effort to advance the project we close all issues older than one year.

If you think this issue needs to be re-evaluated please post a comment on why this is still important and we will re-open it.

We also started an open discussion about the future of Bookshelf.js here #1600. Feel free to drop by and give us your opinion.
Let's make Bookshelf great again