Sequelize - How to setup foreign key and join on it
I have two tables; userPermissions & permissionItems. userPermissions has a permissionItemId as a foreign key, defined as
CONSTRAINT `fk_userPermissions_permissionItemId`
FOREIGN KEY (`permissionItemId`)
REFERENCES `mydb`.`permissionItems` (`permissionItemId`)
Their Sequelize definitions are
const PermissionItem = db.define('permissionItems', {
permissionItemId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
}
});
const UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER,
references: 'permissionItems',
referencesKey: 'permissionItemId'
}
});
UserPermission.hasOne(PermissionItem);
I am then trying to join those two with the and view the results with
UserPermission.findAll({
include: [{
model: PermissionItem
}]
}).then(userPermission => {
console.log('userPermission', userPermission);
});
My expectation here is to get a list of all UserPermissions and outer left join PermissionItem on permissionItemId. Instead, I receive the below error
Unhandled rejection SequelizeDatabaseError: Unknown column
'permissionItem.userPermissionUserPermissionsId' in 'field list'
Have I constructed the query incorrectly? Or the Sequelize definitions?
I have tried flipping these around and including the reverse, and applying the keys backward, in every combination I could think of, all to no avail
sequelize.js
asked Nov 15 '18 at 17:29
Joshua OhanaJoshua Ohana
2,22552567
add a comment |
I have two tables; userPermissions & permissionItems. userPermissions has a permissionItemId as a foreign key, defined as
CONSTRAINT `fk_userPermissions_permissionItemId`
FOREIGN KEY (`permissionItemId`)
REFERENCES `mydb`.`permissionItems` (`permissionItemId`)
Their Sequelize definitions are
const PermissionItem = db.define('permissionItems', {
permissionItemId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
}
});
const UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER,
references: 'permissionItems',
referencesKey: 'permissionItemId'
}
});
UserPermission.hasOne(PermissionItem);
I am then trying to join those two with the and view the results with
UserPermission.findAll({
include: [{
model: PermissionItem
}]
}).then(userPermission => {
console.log('userPermission', userPermission);
});
My expectation here is to get a list of all UserPermissions and outer left join PermissionItem on permissionItemId. Instead, I receive the below error
Unhandled rejection SequelizeDatabaseError: Unknown column
'permissionItem.userPermissionUserPermissionsId' in 'field list'
Have I constructed the query incorrectly? Or the Sequelize definitions?
I have tried flipping these around and including the reverse, and applying the keys backward, in every combination I could think of, all to no avail
sequelize.js
asked Nov 15 '18 at 17:29
Joshua OhanaJoshua Ohana
2,22552567
add a comment |
I have two tables; userPermissions & permissionItems. userPermissions has a permissionItemId as a foreign key, defined as
CONSTRAINT `fk_userPermissions_permissionItemId`
FOREIGN KEY (`permissionItemId`)
REFERENCES `mydb`.`permissionItems` (`permissionItemId`)
Their Sequelize definitions are
const PermissionItem = db.define('permissionItems', {
permissionItemId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
}
});
const UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER,
references: 'permissionItems',
referencesKey: 'permissionItemId'
}
});
UserPermission.hasOne(PermissionItem);
I am then trying to join those two with the and view the results with
UserPermission.findAll({
include: [{
model: PermissionItem
}]
}).then(userPermission => {
console.log('userPermission', userPermission);
});
My expectation here is to get a list of all UserPermissions and outer left join PermissionItem on permissionItemId. Instead, I receive the below error
Unhandled rejection SequelizeDatabaseError: Unknown column
'permissionItem.userPermissionUserPermissionsId' in 'field list'
Have I constructed the query incorrectly? Or the Sequelize definitions?
I have tried flipping these around and including the reverse, and applying the keys backward, in every combination I could think of, all to no avail
sequelize.js
asked Nov 15 '18 at 17:29
Joshua OhanaJoshua Ohana
2,22552567
I have two tables; userPermissions & permissionItems. userPermissions has a permissionItemId as a foreign key, defined as
CONSTRAINT `fk_userPermissions_permissionItemId`
FOREIGN KEY (`permissionItemId`)
REFERENCES `mydb`.`permissionItems` (`permissionItemId`)
Their Sequelize definitions are
const PermissionItem = db.define('permissionItems', {
permissionItemId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
}
});
const UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER,
references: 'permissionItems',
referencesKey: 'permissionItemId'
}
});
UserPermission.hasOne(PermissionItem);
I am then trying to join those two with the and view the results with
UserPermission.findAll({
include: [{
model: PermissionItem
}]
}).then(userPermission => {
console.log('userPermission', userPermission);
});
My expectation here is to get a list of all UserPermissions and outer left join PermissionItem on permissionItemId. Instead, I receive the below error
Unhandled rejection SequelizeDatabaseError: Unknown column
'permissionItem.userPermissionUserPermissionsId' in 'field list'
Have I constructed the query incorrectly? Or the Sequelize definitions?
I have tried flipping these around and including the reverse, and applying the keys backward, in every combination I could think of, all to no avail
sequelize.js
sequelize.js
asked Nov 15 '18 at 17:29
Joshua OhanaJoshua Ohana
2,22552567
asked Nov 15 '18 at 17:29
Joshua OhanaJoshua Ohana
2,22552567
asked Nov 15 '18 at 17:29
Joshua OhanaJoshua Ohana
2,22552567
asked Nov 15 '18 at 17:29
Joshua OhanaJoshua Ohana
2,22552567
asked Nov 15 '18 at 17:29
Joshua OhanaJoshua Ohana
2,22552567
2,22552567
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
In your userPermissions model,
Instead of this
UserPermission.hasOne(PermissionItem);
try this
UserPermission.belongsTo(PermissionItem,{foreignKey:'permissionItemId'});
HasOne and BelongsTo insert the association key in different models
from each other. HasOne inserts the association key in target model
whereas BelongsTo inserts the association key in the source model.
answered Nov 15 '18 at 18:07
Pathum SamararathnaPathum Samararathna
929930
docs.sequelizejs.com/manual/tutorial/associations.html
– Pathum Samararathna
Nov 15 '18 at 18:07
add a comment |
I ended up changing UserPermission to the below. Looks like I don't need to define anything special in the Sequelize model for an existing foreign key in the database, just need to list it in the hasOne call
UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER
}
});
UserPermission.hasMany(PermissionItem, { foreignKey: 'permissionItemId', sourceKey: 'permissionItemId' });
answered Nov 15 '18 at 18:31
Joshua OhanaJoshua Ohana
2,22552567
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53324942%2fsequelize-how-to-setup-foreign-key-and-join-on-it%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
In your userPermissions model,
Instead of this
UserPermission.hasOne(PermissionItem);
try this
UserPermission.belongsTo(PermissionItem,{foreignKey:'permissionItemId'});
HasOne and BelongsTo insert the association key in different models
from each other. HasOne inserts the association key in target model
whereas BelongsTo inserts the association key in the source model.
answered Nov 15 '18 at 18:07
Pathum SamararathnaPathum Samararathna
929930
docs.sequelizejs.com/manual/tutorial/associations.html
– Pathum Samararathna
Nov 15 '18 at 18:07
add a comment |
In your userPermissions model,
Instead of this
UserPermission.hasOne(PermissionItem);
try this
UserPermission.belongsTo(PermissionItem,{foreignKey:'permissionItemId'});
HasOne and BelongsTo insert the association key in different models
from each other. HasOne inserts the association key in target model
whereas BelongsTo inserts the association key in the source model.
answered Nov 15 '18 at 18:07
Pathum SamararathnaPathum Samararathna
929930
docs.sequelizejs.com/manual/tutorial/associations.html
– Pathum Samararathna
Nov 15 '18 at 18:07
add a comment |
In your userPermissions model,
Instead of this
UserPermission.hasOne(PermissionItem);
try this
UserPermission.belongsTo(PermissionItem,{foreignKey:'permissionItemId'});
HasOne and BelongsTo insert the association key in different models
from each other. HasOne inserts the association key in target model
whereas BelongsTo inserts the association key in the source model.
answered Nov 15 '18 at 18:07
Pathum SamararathnaPathum Samararathna
929930
In your userPermissions model,
Instead of this
UserPermission.hasOne(PermissionItem);
try this
UserPermission.belongsTo(PermissionItem,{foreignKey:'permissionItemId'});
HasOne and BelongsTo insert the association key in different models
from each other. HasOne inserts the association key in target model
whereas BelongsTo inserts the association key in the source model.
answered Nov 15 '18 at 18:07
Pathum SamararathnaPathum Samararathna
929930
answered Nov 15 '18 at 18:07
Pathum SamararathnaPathum Samararathna
929930
answered Nov 15 '18 at 18:07
Pathum SamararathnaPathum Samararathna
929930
answered Nov 15 '18 at 18:07
Pathum SamararathnaPathum Samararathna
929930
929930
docs.sequelizejs.com/manual/tutorial/associations.html
– Pathum Samararathna
Nov 15 '18 at 18:07
add a comment |
docs.sequelizejs.com/manual/tutorial/associations.html
– Pathum Samararathna
Nov 15 '18 at 18:07
docs.sequelizejs.com/manual/tutorial/associations.html
– Pathum Samararathna
Nov 15 '18 at 18:07
docs.sequelizejs.com/manual/tutorial/associations.html
– Pathum Samararathna
Nov 15 '18 at 18:07
add a comment |
I ended up changing UserPermission to the below. Looks like I don't need to define anything special in the Sequelize model for an existing foreign key in the database, just need to list it in the hasOne call
UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER
}
});
UserPermission.hasMany(PermissionItem, { foreignKey: 'permissionItemId', sourceKey: 'permissionItemId' });
answered Nov 15 '18 at 18:31
Joshua OhanaJoshua Ohana
2,22552567
add a comment |
I ended up changing UserPermission to the below. Looks like I don't need to define anything special in the Sequelize model for an existing foreign key in the database, just need to list it in the hasOne call
UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER
}
});
UserPermission.hasMany(PermissionItem, { foreignKey: 'permissionItemId', sourceKey: 'permissionItemId' });
answered Nov 15 '18 at 18:31
Joshua OhanaJoshua Ohana
2,22552567
add a comment |
I ended up changing UserPermission to the below. Looks like I don't need to define anything special in the Sequelize model for an existing foreign key in the database, just need to list it in the hasOne call
UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER
}
});
UserPermission.hasMany(PermissionItem, { foreignKey: 'permissionItemId', sourceKey: 'permissionItemId' });
answered Nov 15 '18 at 18:31
Joshua OhanaJoshua Ohana
2,22552567
I ended up changing UserPermission to the below. Looks like I don't need to define anything special in the Sequelize model for an existing foreign key in the database, just need to list it in the hasOne call
UserPermission = db.define('userPermissions', {
userPermissionsId: {
type: Sequelize.INTEGER,
autoIncrement: true,
primaryKey: true
},
permissionItemId: {
type: Sequelize.INTEGER
}
});
UserPermission.hasMany(PermissionItem, { foreignKey: 'permissionItemId', sourceKey: 'permissionItemId' });
answered Nov 15 '18 at 18:31
Joshua OhanaJoshua Ohana
2,22552567
edited Nov 15 '18 at 19:39
answered Nov 15 '18 at 18:31
Joshua OhanaJoshua Ohana
2,22552567
answered Nov 15 '18 at 18:31
Joshua OhanaJoshua Ohana
2,22552567
answered Nov 15 '18 at 18:31
Joshua OhanaJoshua Ohana
2,22552567
2,22552567
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53324942%2fsequelize-how-to-setup-foreign-key-and-join-on-it%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
