How can I model a one-to-one relationship on a primary key in Waterline?

Question

I have a pair of tables (MySQL, if you're curious) such that one is a primary table and one is an auxiliary table whose PK is an FK to the first one -- they share a one-to-one relationship.

I've defined both tables adequately, but I run into trouble when I attempt to define the primary table's model to refer to the secondary. Specifically, all attempts to do /this/ fail:

/* existing primary key defined, works okay */
id: {
    columnName: 'KeyID',
    type: 'integer',
    primaryKey: 'true',
    autoIncrement: 'true'
},

/* adding this ruins everything */
moreData: {
    columnName: 'KeyID',
    model: 'extraData'
}

Running this sees the ORM /return/ but the results are strange and corrupt, with KeyID being a copy of what moreData should be, and moreData being a strange array with DB fields and not properly rename attributes. Excluding the columnName sees the query fail because moreData isn't in the primary table's field list.

Am I approaching this incorrectly?

score 0 · Answer 1 · answered Apr 24 '15 at 17:20

Let me know if I'm not understanding this correctly. It seems that what you have is two models, PrimaryData and ExtraData, that should be in a one-to-one relationship. Is that right?

If so, I would set this up as follows:

//PrimaryData.js
module.exports = {

  attributes: {

    id: {
      column: 'KeyID',
      type: 'integer',
      primaryKey: true,
      autoIncrement: true
    },
    extraData: {
      column: 'ExtraKeyID',
      model: 'ExtraData'
    }
  }
}

//ExtraData.js
module.exports = {

  attributes: {

    id: {
      column: 'KeyID',
      type: 'integer',
      primaryKey: true,
      autoIncrement: true
    },
    primaryData: {
      column: 'PrimaryKeyID',
      model: 'PrimaryData'
    }
  }
}

This sets up a one-to-one relationship between the two data models.

Note that one-to-one relationships in Waterline are not currently updated automatically in both tables.

https://github.com/balderdashy/waterline/issues/360

So depending on your needs, you could change this to a one-to-many relationship.

If ExtraData is really just an extension of PrimaryData, and will never be used on it's own, then one-to-one is probably the way to go, and you don't even need the pointer back to PrimaryData in the ExtraData model.

//ExtraData.js
module.exports = {

  attributes: {

    id: {
      column: 'KeyID',
      type: 'integer',
      primaryKey: true,
      autoIncrement: true
    },
  }
}

Hope I understood your problem correctly and that this helps.

Unfortunately, CTC, not quite -- the table is one to one on the /primary key/. You've posited the existence of "ExtraKeyID", but that's never existed -- the second table is an extension of the first table and keyed by KeyID. The attempt to use the same column multiple times seems to make Waterline wonky, and I'm trying to work out if I've modeled it wrong or Waterline just won't play ball. (I coulld just instantiate both objects seperately, and maybe I'll have to, but everything I can do with ORM I want to.) But thanks for taking a crack at it! — jesdynf, Apr 25 '15 at 23:18

How can I model a one-to-one relationship on a primary key in Waterline?

1 Answers1