I was very confused in selecting employee and leave data using the master number contained in both tables. Can someone help me, here is code:
karyawan model file:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Karyawan = sequelize.define('Karyawan', {
nomorInduk: DataTypes.STRING,
nama: DataTypes.STRING,
alamat: DataTypes.STRING,
tanggalLahir: DataTypes.DATE,
tanggalBergabung: DataTypes.DATE
}, {});
Karyawan.associate = function (models) {
Karyawan.hasMany(models.Cuti, { foreignKey: 'nomorInduk' });
};
return Karyawan;
};
index model file:
'use strict';
const Sequelize = require('sequelize');
const dotenv = require('dotenv');
const db = {};
const KaryawanModel = require('./karyawan');
const CutiModel = require('./cuti');
dotenv.config();
const sequelize = new Sequelize(process.env.DB_NAME, process.env.DB_USER, process.env.DB_PASSWORD, {
host: process.env.DB_HOST,
dialect: 'mysql',
logging: console.log
});
sequelize.sync({ force: false })
.then(() => {
console.log('Database synchronized');
})
.catch((error) => {
console.error('Failed to synchronize database:', error);
});
const Karyawan = KaryawanModel(sequelize, Sequelize);
const Cuti = CutiModel(sequelize, Sequelize);
db.sequelize = sequelize;
db.Sequelize = Sequelize;
// Set up associations inside the models
Karyawan.associate({ Cuti });
Cuti.associate({ Karyawan });
module.exports = { db, Karyawan, Cuti };
cuti model file:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Cuti = sequelize.define('Cuti', {
nomorInduk: DataTypes.STRING,
tanggalCuti: DataTypes.DATE,
lamaCuti: DataTypes.INTEGER,
keterangan: DataTypes.TEXT
}, {});
Cuti.associate = function (models) {
Cuti.belongsTo(models.Karyawan, { foreignKey: 'nomorInduk' });
};
return Cuti;
};
handler to get karyawan and cuti data:
async function getKaryawanByNomorInduk(req, res) {
try {
const nomorInduk = req.params.nomorInduk;
const karyawan = await models.Karyawan.findOne({
where: {
nomorInduk: nomorInduk
},
include: [{
model: models.Cuti,
attributes: ['id', 'nomorInduk', 'tanggalCuti', 'lamaCuti', 'keterangan'],
}],
});
if (!karyawan) {
return res.status(404).json({ message: 'Tidak ada data karyawan dengan nomor induk tersebut.' });
}
const result = {
status: 'ok',
data: karyawan,
};
res.json(result);
} catch (error) {
res.status(500).json({ message: 'Gagal mendapatkan data karyawan.', error: error.message });
}
}
i always get result like this: Result I got
and what I want is a result like this: The result I want
Since you indicated only foreignKey: 'nomorInduk'
then Cuti
will be linked to Karyawan
like this: Cuti.nomorInduk=Karyawan.id because if you didn't indicate the key for the 1 table in 1:n relationship Sequelize automatically use the primary key field which is id
in this case. Simply indicate the other side key explicitly in both hasMany
and belongsTo
associations:
Cuti.belongsTo(models.Karyawan, { foreignKey: 'nomorInduk', targetKey: 'nomorInduk' });
Karyawan.hasMany(models.Cuti, { foreignKey: 'nomorInduk', sourceKey: 'nomorInduk' });
Please pay attention that in order to make such relationship working with non-default master table key field you need to add a unique index on the nomorInduk
column in Karyawan
table.