Attach with UpdatePivot for "Not Null" column

Question

Using bookshelfjs, I would like to add record to a join table that contains additional, not nullable column.

For example, a users table and an accounts table would have a joining table of accounts_users.

var Account = Bookshelf.Model.extend({
  tableName: 'accounts'
});

var User = Bookshelf.Model.extend({

  tableName: 'users',

  accounts: function () {
    return this.belongsToMany(Account);
  }
});

The join table also has a column "order" NOT NULL.

CREATE TABLE accounts_users
(
  account_id integer NOT NULL,
  user_id integer NOT NULL,
  "order" integer NOT NULL,
  CONSTRAINT accounts_users_account_id_foreign FOREIGN KEY (account_id)
      REFERENCES accounts (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT accounts_users_user_id_foreign FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

Is it possible to attach a record in Users to a record in Accounts, and set the order value at the same time?

Each one can be done separately using:

user.related("accounts").attach(<USER_ID>);

and

user.related("accounts").updatePivot({order: 1}); // with WHERE clause.

Running each command separately will fail since the first one will not fulfill the NOT NULL restriction. Is there a way to execute both commands together using bookshelf? It can be easily done using Knex.Raw but I would like to do it using bookshelf if possible.

Answer 1

You can pass name/value pairs into .attach to have those values set on the join table record. See the example in the first part of this GitHub issue:

https://github.com/tgriesser/bookshelf/issues/134

user.related('accounts').attach({ account_id: 42, user_id: 84, order: 1})