i am trying to join tables in node sequelize.try get datas from these two tables but its not working properly i think some problems in my code please help me to solve this problems using linux mint. started node sequelize in few days.now using postgres database
emploee table
const Sequelize = require("sequelize");
const db = require("../config/database");
const dataType = db.sequelize;
const Employee = dataType.define("employee", {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
username: {
type: Sequelize.STRING,
},
password: {
type: Sequelize.STRING
},
employeename:{
type:Sequelize.STRING
},
});
module.exports = Employee;
user table
const Sequelize = require("sequelize"); //use sequelize
const db=require("../config/database"); //connect sequelize
const dataType = db.sequelize;
const User = dataType.define("user",{ //table name
id:{
type:Sequelize.INTEGER, //crete columns
autoIncrement:true,
primaryKey:true
},
username:{
type:Sequelize.STRING
},
email:{
type:Sequelize.STRING
}
});
module.exports = User; //export table
service.js
exports.views= req =>{
console.log("enterd get service",req.body);
return User.findAll({
include:[
{
model:Employee
}
]
}).then(User =>{
console.log("user data is=",User);
const resobj= User.map(user=>{
return Object.assign(
{},
{
email:User.email,
sarath:user.sarath.map(Employee=>{
return Object.assign(
{},
{
username:Employee.username,
employeename:Employee.employeename,
}
)
})
}
)
});
return resobj;
}).catch(err=>{
return console.log("ERR IS",err);
});
};
sequelize
const Sequelize = require("sequelize");
// const Op = Sequelize.Op;
const Op=Sequelize.Op;
const sequelize = new Sequelize(
"postgres", //db name
"postgres", //username
"postgres", //password
{
host: "localhost",
dialect: "postgres",
// operatorsAliases: false,
opertorsAliases:{
$or:Op.or
},
pool: {
max: 5,
min: 0,
require: 30000,
idle: 1000
},
define: {
freezeTableName: true
}
}
);
// define model/table
const models = {
Employee: sequelize.define("../model/employee"),
User:sequelize.define("../model/user")
};
//relations
models.Employee.hasMany(models.User);
models.Sequelize = Sequelize;
models.sequelize = sequelize;
module.exports = models;
this is my vs code output
enterd get service {}
ERR IS { SequelizeEagerLoadingError: employee is not associated to user!
at Function._getIncludedAssociation (/home/sarath/Desktop/asset/node_modules/sequelize/lib/model.js:711:13)
at Function._validateIncludedElement (/home/sarath/Desktop/asset/node_modules/sequelize/lib/model.js:615:53)
at options.include.options.include.map.include (/home/sarath/Desktop/asset/node_modules/sequelize/lib/model.js:511:37)
at Array.map (<anonymous>)
at Function._validateIncludedElements (/home/sarath/Desktop/asset/node_modules/sequelize/lib/model.js:506:39)
at Promise.try.then.then (/home/sarath/Desktop/asset/node_modules/sequelize/lib/model.js:1711:14)
at tryCatcher (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/util.js:16:23)
at Promise._settlePromiseFromHandler (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/promise.js:512:31)
at Promise._settlePromise (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/promise.js:569:18)
at Promise._settlePromise0 (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/promise.js:614:10)
at Promise._settlePromises (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/promise.js:694:18)
at _drainQueueStep (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/async.js:138:12)
at _drainQueue (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/async.js:131:9)
at Async._drainQueues (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/async.js:147:5)
at Immediate.Async.drainQueues [as _onImmediate] (/home/sarath/Desktop/asset/node_modules/bluebird/js/release/async.js:17:14)
at processImmediate (timers.js:632:19) name: 'SequelizeEagerLoadingError' }
GET /abc/users 200 20.215 ms - 57
i think it might be easier for you if you declare your associations in your classes as per the documentation.. but you'll have to add some stuff to get it to work..
I don't really get why there would be a hasMany relationship between Employee and User, there is nothing in your models that would suggest that... Your models suggest that a User belongsTo Employee, and Employee hasOne User but because it is not by a foreign key id you will have to tell sequelize what keys to use to make the association...
Maybe something along the lines of
module.exports = function (sequelize, DataTypes) {
const Employee = sequelize.define('employee', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
username: {
type: Sequelize.STRING,
},
password: {
type: Sequelize.STRING
},
employeename:{
type:Sequelize.STRING
},
},
}, {
freezeTableName: true,
tableName: 'employee',
});
Employee.associate = function (models) {
models.Employee.hasOne(models.User, { as: 'Employee', foreignKey: 'username', sourceKey: 'username' });
};
return Employee;
};
module.exports = function (sequelize, DataTypes) {
const User = sequelize.define('user', {
id:{
type:Sequelize.INTEGER, //crete columns
autoIncrement:true,
primaryKey:true
},
username:{
type:Sequelize.STRING
},
email:{
type:Sequelize.STRING
}
},
}, {
freezeTableName: true,
tableName: 'user',
});
User.associate = function (models) {
models.User.belongsTo(models.Employee, { as: 'User', foreignKey: 'username', sourceKey: 'username' });
};
return User;
};
and then where you did this: you need to add the part you were missing
// define model/table
const models = {
Employee: sequelize.import('../model/employee'),
User:sequelize.import('../model/user')
};
//You were missing this
Object.keys(models).forEach((modelName) => {
if ('associate' in models[modelName]) {
// console.log(models[modelName]);
models[modelName].associate(models);
}
});
models.Sequelize = Sequelize;
models.sequelize = sequelize;
I doubt you can copy and paste this and have it work without a few fixes as i just typed it in here and didn't check it in VSCode but this is the jist of it..