node.jsexpresssequelize.jssequelize-cli

Cannot select multiple tables using sequelize


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


Solution

  • 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.