Multiple column foreign key and associations?

[olalonde]

I have an outputs table with the following columns: tx_hash and position which are its primary key and an inputs table with following columns: txout_tx_hash and txout_position which respectively reference outputs.tx_hash and outputs.position.

I would like to have an 'output' relationship on my Input model that I could load like this:

someInput.load([ 'output' ]).then(function () { ... });

I tried the following without success:

Input = bookshelf.model( //....
    output: function () {
      return this.belongsTo(Output, [ 'outputs', 'tx_hash', 'position' ]);
    }

//....
)

// Results in this error:

{ [error: column outputs.null does not exist]
  name: 'error',
  length: 103,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '41',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '2639',
  routine: 'errorMissingColumn' }

Is there any way I can define the relationship so that I will be able to load it with the .load() method? Also is it possible to use multiple columns for idAttribute?

[rhys-vdw]

First, this is not supported:

    output: function () {
      return this.belongsTo(Output, [ 'outputs', 'tx_hash', 'position' ]);
    }

belongsTo only accepts two arguments. The first being the related model (or a model name if you're using the registry plugin). The second is the model's column that references the primary key of the related model.

If I understand correctly you're trying to define a relationship with a composite primary key. ie. ...WHERE outputs.tx_hash = inputs.txout_hash AND outputs.position = inputs.txout_position.... Currently this is not supported, however it is certainly a feature we'd like to support.

For now - in short - the answer is no. You cannot use load with a relation that has two primary keys. Best workaround I'm aware of is to give the table a primary key index and apply a UNIQUE constraints to your two FK's.

[olalonde]

Ok thanks.

[olalonde]

@rhys-vdw Is there a roadmap for future versions of bookshelf?

[rhys-vdw]

@olalonde Nothing formal yet. Just an idea of where we want to go and what we primarily want ot address. The creation of a roadmap will be preceded by a community discussion, and the plans will all be open for comment.