Cannot select multiple tables using sequelize

Question

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

Can you please convert the code screenshots to code snippets so that people who depend on screen readers have a chance to read them? Thanks! — exa
– exa, Commented Jan 4, 2024 at 20:31
Is it possible that sequelize confused when trying to get where condition since you have show "nomorInduk" in your karyawan model and cuti model? have you try to not add "nomorInduk" in your models.Cuti attributes in getKaryawanByNomorInduk function? — Firofa
– Firofa, Commented Jan 5, 2024 at 7:39

Anatoly · Accepted Answer · 2024-01-05 16:20:29Z

0

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.

answered Jan 5, 2024 at 16:20

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot select multiple tables using sequelize

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related