One to One associations

[SeanFacer]

Sails version:1.1.0 Node version:v11.7.0 NPM version:6.8.0 DB adapter name: mysql DB adapter version: 5.7 Operating system:mac

---

Hello,

I have an existing database for a website which I'm connecting sails to.

There are two tables I am trying to associate with a one to one relationship or a one way association. Both connect via a product_id field.

I've tried all different combinations from the documentation of the setttings provided. Each one giving a different error. Included some examples below.

Trimmed for convenience - options and errors. Option 1 Error: no association returned

module.exports = {
  tableName: 'oc_product',
  attributes: {
    createdAt: false,
    updatedAt: false,
    id: {
      type: "number",
      columnType: "int",
      columnName: "product_id",
      isInteger: true,
      autoIncrement: true,
      unique: true
    },
    oc_product_description: {
      collection: 'ocProductDescription',
      via: 'product_id'
    }
  }
};

module.exports = {
  datastore: 'iclicksee',
  tableName: 'oc_product_description',
  attributes: {
    createdAt: false,
    updatedAt: false,
    product_id: {
      unique: true,
      model: 'ocProducts',
    },
  }
}

Option 2 Error: The model ocproductdescription defined a primary key of product_id that has an invalid type. Valid primary key types are number and string Note: if I set the primary type it then complains that this should be picked up automatically by association

module.exports = {
  tableName: 'oc_product',
  primaryKey: 'product_id',
  attributes: {
    createdAt: false,
    updatedAt: false,
    product_id: {
      type: "number",
      columnType: "int",
      isInteger: true,
      autoIncrement: true,
      unique: true
    },
    oc_product_description: {
      model: 'ocProductDescription',
    }
  }
}

module.exports = {
  datastore: 'iclicksee',
  tableName: 'oc_product_description',
  primaryKey: 'product_id',
  attributes: {
    createdAt: false,
    updatedAt: false,
    product_id: {
      unique: true,
      model: 'ocProducts',
    },
  }
} 

Option 3 Error: Unexpected error from database adapter: ER_BAD_FIELD_ERROR: Unknown column 'oc_product.oc_product_description' in 'field list'

module.exports` = {
  tableName: 'oc_product',
  primaryKey: 'product_id',
  attributes: {
    createdAt: false,
    updatedAt: false,
    product_id: {
      type: "number",
      columnType: "int",
      isInteger: true,
      autoIncrement: true,
      unique: true
    },
    oc_product_description: {
      model: 'ocProductDescription',
    }
  }
}

module.exports = {
  datastore: 'iclicksee',
  tableName: 'oc_product_description',
  primaryKey: 'product_id',
  attributes: {
    createdAt: false,
    updatedAt: false,
    product_id: {
      type: "number",
      unique: true,
    },
  }
}

Option 4: Error: Unexpected error from database adapter: ER_BAD_FIELD_ERROR: Unknown column 'oc_product.oc_product_description' in 'field list'

module.exports = {
  tableName: 'oc_product',
  attributes: {
    createdAt: false,
    updatedAt: false,
    id: {
      type: "number",
      columnType: "int",
      columnName: "product_id",
      isInteger: true,
      autoIncrement: true,
      unique: true
    },
    oc_product_description: {
      model: 'ocProductDescription',
    }
  }
};

module.exports = {
  datastore: 'iclicksee',
  tableName: 'oc_product_description',
  attributes: {
    createdAt: false,
    updatedAt: false,
    id: {
       columnName: 'product_id',
       unique: true,
       type: "number",
      model: 'ocProducts',
    },
  }
}

[sailsbot]

Hi @SeanFacer! It looks like you missed a step or two when you created your issue. Please edit your comment (use the pencil icon at the top-right corner of the comment box) and fix the following:

• Provide your Sails version
• Provide your Node version
• Provide your NPM version
• Provide your DB adapter name
• Provide your DB adapter version
• Provide your Operating system
• Verify "I have tried all the following (if relevant) and my issue remains:"

As soon as those items are rectified, post a new comment (e.g. “Ok, fixed!”) below and we'll take a look. Thanks!

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact inquiries@sailsjs.com

[SeanFacer]

Ok, fixed!

[sailsbot]

Sorry to be a hassle, but it looks like your issue is still missing some required info. Please double-check your initial comment and try again.

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact inquiries@sailsjs.com

[SeanFacer]

Ok, fixed!

[sailsbot]

Sorry to be a hassle, but it looks like your issue is still missing some required info. Please double-check your initial comment and try again.

*If you feel this message is in error, or you want to debate the merits of my existence (sniffle), please contact inquiries@sailsjs.com

[SeanFacer]

Ok, fixed

[sailsbot]

@SeanFacer Thanks for posting, we'll take a look as soon as possible.

---

For help with questions about Sails, click here. If you’re interested in hiring @sailsbot and her minions in Austin, click here.

[SeanFacer]

Adding more info.

Please see below both model configs.

If I log QueryOptions from a healthy call and the erroneus call I get two different outputs for the populate.

sails/lib/hooks/blueprints/actions/find.js:36:4

Healthy call

api_1         | { using: 'products',
api_1         |   populates: { category_id: {} },
api_1         |   criteria: { where: {}, limit: 30 } }

Erroneous call

api_1         | { using: 'ocproducts',
api_1         |   populates:
api_1         |    { ocProductDescription: { where: {}, limit: 30, skip: 0, select: [Array], omit: [] } },
api_1         |   criteria: { where: {}, limit: 30 } }

Heres a stack trace of the issue.

api_1         | error: Sending 500 ("Server Error") response:
api_1         |  AdapterError: Unexpected error from database adapter: Cannot read property 'definition' of undefined
api_1         |     at Object.findRecords (/usr/src/app/node_modules/sails/lib/hooks/blueprints/actions/find.js:36:4)
api_1         |     at /usr/src/app/node_modules/sails/lib/router/bind.js:248:46
api_1         |     at routeTargetFnWrapper (/usr/src/app/node_modules/sails/lib/router/bind.js:392:9)
api_1         |     at Layer.handle [as handle_request] (/usr/src/app/node_modules/express/lib/router/layer.js:95:5)
api_1         |     at next (/usr/src/app/node_modules/express/lib/router/route.js:137:13)
api_1         |     at Route.dispatch (/usr/src/app/node_modules/express/lib/router/route.js:112:3)
api_1         |     at Layer.handle [as handle_request] (/usr/src/app/node_modules/express/lib/router/layer.js:95:5)
api_1         |     at /usr/src/app/node_modules/express/lib/router/index.js:281:22
api_1         |     at Function.process_params (/usr/src/app/node_modules/express/lib/router/index.js:335:12)
api_1         |     at next (/usr/src/app/node_modules/express/lib/router/index.js:275:10)
api_1         |     at next (/usr/src/app/node_modules/express/lib/router/route.js:127:14)
api_1         |     at /usr/src/app/api/policies/isAuthorized.js:29:7
api_1         |     at tryCatcher (/usr/src/app/node_modules/parley/node_modules/bluebird/js/release/util.js:11:23)
api_1         |     at Promise._settlePromiseFromHandler (/usr/src/app/node_modules/parley/node_modules/bluebird/js/release/promise.js:491:31)
api_1         |     at Promise._settlePromise (/usr/src/app/node_modules/parley/node_modules/bluebird/js/release/promise.js:548:18)
api_1         |     at Promise._settlePromise0 (/usr/src/app/node_modules/parley/node_modules/bluebird/js/release/promise.js:593:10)
api_1         |     at Promise._settlePromises (/usr/src/app/node_modules/parley/node_modules/bluebird/js/release/promise.js:676:18)
api_1         |     at Promise._fulfill (/usr/src/app/node_modules/parley/node_modules/bluebird/js/release/promise.js:617:18)
api_1         |     at /usr/src/app/node_modules/parley/node_modules/bluebird/js/release/nodeback.js:42:21
api_1         |     at proceedToFinalAfterExecLC (/usr/src/app/node_modules/parley/lib/private/Deferred.js:1151:14)
api_1         |     at proceedToInterceptsAndChecks (/usr/src/app/node_modules/parley/lib/private/Deferred.js:909:12)
api_1         |     at proceedToAfterExecSpinlocks (/usr/src/app/node_modules/parley/lib/private/Deferred.js:841:10)
api_1         |     at /usr/src/app/node_modules/parley/lib/private/Deferred.js:303:7
api_1         |     at _afterPotentiallyRunningAfterLC (/usr/src/app/node_modules/waterline/lib/waterline/methods/find-one.js:329:20)
api_1         |     at _maybeRunAfterLC (/usr/src/app/node_modules/waterline/lib/waterline/methods/find-one.js:323:20)
api_1         |     at _afterFetchingRecords (/usr/src/app/node_modules/waterline/lib/waterline/methods/find-one.js:325:13)
api_1         |     at _afterGettingPopulatedPhysicalRecords (/usr/src/app/node_modules/waterline/lib/waterline/utils/query/help-find.js:754:12)
api_1         |     at /usr/src/app/node_modules/waterline/lib/waterline/utils/query/help-find.js:151:16
api_1         |     at Object.success (/usr/src/app/node_modules/sails-mysql/lib/adapter.js:190:18)
api_1         |     at /usr/src/app/node_modules/machine/lib/private/help-build-machine.js:1517:30
api_1         |     at proceedToFinalAfterExecLC (/usr/src/app/node_modules/parley/lib/private/Deferred.js:1151:14)
api_1         |     at proceedToInterceptsAndChecks (/usr/src/app/node_modules/parley/lib/private/Deferred.js:909:12)
api_1         |     at proceedToAfterExecSpinlocks (/usr/src/app/node_modules/parley/lib/private/Deferred.js:841:10)
api_1         |     at /usr/src/app/node_modules/parley/lib/private/Deferred.js:303:7

oc_product_description model

/**
    Generated by sails-inverse-model
    Date:Fri Mar 01 2019 16:33:42 GMT+0000 (Coordinated Universal Time)
*/

module.exports = {
  datastore: 'iclicksee',
  tableName: 'oc_product_description',
  primaryKey: 'language_id',
  // primaryKey: 'product_id',
  attributes: {
    createdAt: false,
    updatedAt: false,
    // id: {
    //   columnName: 'product_id',
    //   unique: true,
    //   type: "number",
    //   // model: 'ocProducts',
    // },
    // product_id: {
    //   type: "number",
    //   unique: true,
    // },
    product_id: {
      model:'ocproducts'
    },
    language_id: {
      type: "number",
      columnType: "int",
      isInteger: true,
      required: true
    },
    name: {
      type: "string",
      columnType: "varchar",
      maxLength: 255,
      required: true
    },
    description: {
      type: "string",
      columnType: "text",
      required: true
    },
    tag: {
      type: "string",
      columnType: "text",
      required: true
    },
    meta_title: {
      type: "string",
      columnType: "varchar",
      maxLength: 255,
      required: true
    },
    meta_description: {
      type: "string",
      columnType: "varchar",
      maxLength: 255,
      required: true
    },
    meta_keyword: {
      type: "string",
      columnType: "varchar",
      maxLength: 255,
      required: true
    }
  }
};

oc_product model

/**
    Generated by sails-inverse-model
    Date:Fri Mar 01 2019 16:33:42 GMT+0000 (Coordinated Universal Time)
*/
module.exports = {
  datastore: 'iclicksee',
  tableName: 'oc_product',
  // primaryKey: 'product_id',
  attributes: {
    createdAt: false,
    updatedAt: false,
    id: {
      type: "number",
      columnType: "int",
      columnName: "product_id",
      isInteger: true,
      autoIncrement: true,
      unique: true
    },
    // product_id: {
    //   type: "number",
    //   columnType: "int",
    //   isInteger: true,
    //   autoIncrement: true,
    //   unique: true
    // },
    model: {
      type: "string",
      columnType: "varchar",
      maxLength: 64,
      required: true
    },
    sku: {
      type: "string",
      columnType: "varchar",
      maxLength: 64,
      required: true
    },
    upc: {
      type: "string",
      columnType: "varchar",
      maxLength: 12,
      required: false,
      allowNull: true
    },
    ean: {
      type: "string",
      columnType: "varchar",
      maxLength: 14,
      required: false
    },
    jan: {
      type: "string",
      columnType: "varchar",
      maxLength: 13,
      required: false
    },
    isbn: {
      type: "string",
      columnType: "varchar",
      maxLength: 17,
      required: false
    },
    mpn: {
      type: "string",
      columnType: "varchar",
      maxLength: 64,
      required: false
    },
    location: {
      type: "string",
      columnType: "varchar",
      maxLength: 128,
      required: true
    },
    quantity: {
      type: "number",
      columnType: "int",
      isInteger: true,
      required: true
    },
    stock_status_id: {
      type: "number",
      columnType: "int",
      isInteger: true,
      required: true
    },
    image: {
      type: "string",
      columnType: "varchar",
      maxLength: 255,
      required: true
    },
    manufacturer_id: {
      type: "number",
      columnType: "int",
      isInteger: true,
      required: true
    },
    shipping: {
      type: "number",
      columnType: "tinyint",
      isInteger: true,
      defaultsTo: 1
    },
    price: {
      type: "number",
      columnType: "decimal",
      defaultsTo: 0.0000
    },
    points: {
      type: "number",
      columnType: "int",
      isInteger: true,
      defaultsTo: 0
    },
    tax_class_id: {
      type: "number",
      columnType: "int",
      isInteger: true,
      required: true
    },
    date_available: {
      type: "string",
      columnType: "date",
      defaultsTo: "0000-00-00"
    },
    weight: {
      type: "number",
      columnType: "decimal",
      defaultsTo: 0.00000000
    },
    weight_class_id: {
      type: "number",
      columnType: "int",
      isInteger: true,
      defaultsTo: 0
    },
    length: {
      type: "number",
      columnType: "decimal",
      defaultsTo: 0.00000000,
      allowNull: true
    },
    width: {
      type: "number",
      columnType: "decimal",
      defaultsTo: 0.00000000,
      allowNull: true
    },
    height: {
      type: "number",
      columnType: "decimal",
      defaultsTo: 0.00000000,
      allowNull: true
    },
    length_class_id: {
      type: "number",
      columnType: "int",
      isInteger: true,
      defaultsTo: 0,
      allowNull: true
    },
    subtract: {
      type: "number",
      columnType: "tinyint",
      isInteger: true,
      defaultsTo: 1,
      allowNull: true
    },
    minimum: {
      type: "number",
      columnType: "int",
      isInteger: true,
      defaultsTo: 1,
      allowNull: true
    },
    sort_order: {
      type: "number",
      columnType: "int",
      isInteger: true,
      defaultsTo: 0,
      allowNull: true
    },
    status: {
      type: "number",
      columnType: "tinyint",
      isInteger: true,
      defaultsTo: 0
    },
    viewed: {
      type: "number",
      columnType: "int",
      isInteger: true,
      defaultsTo: 0
    },
    date_added: {
      type: "string",
      columnType: "datetime",
      required: true
    },
    date_modified: {
      type: "string",
      columnType: "datetime",
      required: true
    },
    ocProductDescription: {
      collection: 'ocProductDescription',
      via: 'product_id'
    }
  }
};

[SeanFacer]

@johnabrams7 yo whats the expected behaviour here, whats the required behaviour. Is it something I can help try fix.

[rachaelshaw]

@SeanFacer I noticed you had model: 'ocProducts' in the attribute definitions in the example code, but then referred to it later as the oc_product model. Is there any chance that the model identity may not be plural? (i.e. 'ocProduct' instead of 'ocProducts')

If that's not the case, would you be able to create an example repo reproducing your issue? That would help give us better context for what you're trying to do, and what might be causing this behavior.

On a related note, while trying to test out your examples, I did notice that sails-disk has a bug where it's not picking up on required: true for that type of association; will take a look at fixing that (although it looks like your'e not using sails-disk, so this shouldn't affect you).

[rachaelshaw]

@SeanFacer after fixing my local sails-disk and trying out your first example again in a new project, the association worked for me. However, something to be aware of with a "Has one" association using a collection is that you need to call .populate() to get the associated record (more info here).