Reputation: 1356
I have a MySQL database that was previously used for production, and right now our team is migrating to SailsJS. I've read about sails' association and I think it is great. But I was wondering if you can use the association to populate the connected tables using the .populate
method. I've tried it by adding
user: {
model: 'User'
}
to my profile model. But when I tried populating it by using the populate method. It generates an error.
"error": "E_UNKNOWN",
"status": 500,
"summary": "Encountered an unexpected error",
"raw": {
"code": "ER_BAD_FIELD_ERROR",
"errno": 1054,
"sqlState": "42S22",
"index": 0
}
Here are the two mysql table schemas For the user table:
CREATE TABLE IF NOT EXISTS `user` (
`userId` int(20) NOT NULL AUTO_INCREMENT,
`email` varchar(40) NOT NULL,
`password` varchar(60) NOT NULL,
`locationId` int(20) DEFAULT NULL,
`status` tinyint(4) NOT NULL COMMENT '0 - inactive, 1 - active, 2 - delete',
`companyId` int(20) DEFAULT NULL,
`createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`userId`),
UNIQUE KEY `email` (`email`),
KEY `fk_locationId` (`locationId`),
KEY `fk_orgId` (`companyId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=259 ;
For the profile table:
CREATE TABLE IF NOT EXISTS `profile` (
`profileId` int(11) NOT NULL AUTO_INCREMENT,
`firstName` varchar(30) NOT NULL,
`lastName` varchar(30) NOT NULL,
`suffix` varchar(10) DEFAULT NULL,
`nickName` varchar(25) DEFAULT NULL,
`title` varchar(45) DEFAULT NULL COMMENT 'Name Title, e.g, Dr., Engr., etc\n',
`userId` int(11) NOT NULL,
`birthDate` date DEFAULT NULL,
`phoneNumber` varchar(30) DEFAULT NULL,
`dateUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`image` tinyint(1) NOT NULL DEFAULT '0',
`gender` tinyint(1) NOT NULL COMMENT '0 - female, 1 - male',
`middleName` varchar(20) DEFAULT NULL,
`telephoneNumber` varchar(30) DEFAULT NULL,
PRIMARY KEY (`profileId`),
KEY `fk_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=259 ;
ALTER TABLE `profile`
ADD CONSTRAINT `fk_userId` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`) ON DELETE CASCADE ON UPDATE CASCADE;
Update
User Model
Profile Model
Upvotes: 3
Views: 1784
Reputation: 24958
It looks like you may not be using the latest version of Sails beta (rc7), which may have provided you with slightly better error messages (like which field was causing the error).
In any case, it would appear that you need a bit of database migration to get things working with Sails. You can have Waterline do this for you--in fact it will do it by default if you don't have the migrate: safe
attribute in your connection configuration. But in your case, as is often true when migrating projects, you already have a schema and you'd probably rather not have Waterline mess with it. No problem--we just need to tweak some settings in your model configurations and you should be good to go.
In api/models/Profile.js:
module.exports = {
autoPK: false, // don't try and add a unique ID; we already have one
autoCreatedAt: false, // don't try and add a createdAt timestamp
autoUpdatedAt: false, // don't try and add a updatedAt timestamp
attributes: {
profileId: {
type: 'integer',
primaryKey: true
},
user: {
model: 'user',
columnName: 'userId'
},
...etc...
}
}
In api/models/User.js:
module.exports = {
autoPK: false, // don't try and add a unique ID; we already have one
autoCreatedAt: false, // don't try and add a createdAt timestamp
autoUpdatedAt: false, // don't try and add a updatedAt timestamp
attributes: {
userId: {
type: 'integer',
primaryKey: true
}
profile: {
model: 'profile',
columnName: 'profileId'
},
...etc...
}
}
Note that Sails doesn't currently support true one-to-one relationships, so you'll have to link both sides individually if you want to populate both ways. That is, adding a profile with user: 1
won't let you do User.findOne(1).populate('profile')
; you'll have to set the profile
key on user #1 explicitly in order for that to work.
Upvotes: 2