Sequelize: Error trying to nested association

Question

Hello i'm new with Sequelize and i'm really lost about how to manage relations. I want to set 1:N relation but when I see the results I'm not receiving the relation data. I'm working with 2 tables at this moment, medicos and hospitals where hospitals can have many doctors but doctors only has one hospital.

This is my doctors table: models/doctors.js

    module.exports = function(sequelize, DataTypes) {
      return sequelize.define('doctors', {
        id: {
          type: DataTypes.INTEGER(11),
          allowNull: false,
          primaryKey: true,
          autoIncrement: true
        },
        name: {
          type: DataTypes.STRING,
          allowNull: false
        },
        lastName: {
          type: DataTypes.STRING,
          allowNull: false
        },
        SSN: {
          type: DataTypes.STRING,
          allowNull: false
        },
        speciality: {
          type: DataTypes.ENUM('Doctor','Geriatrician'),
          allowNull: false
        },
        idHospital: {
          type: DataTypes.INTEGER(11),
          allowNull: false,
        },
      }, {
        tableName: 'doctors',
        freezeTableName: true,
        classMethods: {
          associate: models =>  {
            models.doctors.belongsTo(models.hospitals,  {
              foreignKey: "idHospital"
            })
         }
       }
  });
      });
    };

and this one is hospitals one: models/hospitals.js

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('hospitals', {
    id: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    idData: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      references: {
        model: 'data',
        key: 'id'
      }
    },
  }, {
    tableName: 'hospitals',
    freezeTableName: true,
    classMethods: {
      associate: models =>  {
      models.hospitals.hasMany(models.doctors,  {
         foreignKey: "idHospital"
      })
    }
  }
  });
};

Im managing my models with this file models/index.js

'use strict'

module.exports = (connection) => {
  const Users                     = connection.import('users'),
        States                    = connection.import('states'),
        Cities                    = connection.import('cities'),
        Data                      = connection.import('data'),
        Patient                   = connection.import('patients'),
        Hospitals                 = connection.import('hospitals'),
        Doctors                   = connection.import('doctors'),


        Doctors.belongsTo(Hospitals)
        Hospitals.hasMany(Doctors)
        require('../controllers/patients')(Users)
        require('../controllers/personal')(Doctors, Hospitals)
}

this one is my controller /controllers/personal.js

'use strict'

module.exports = (Doctors) =>  {

  const express   = require('express'),
        router    = express.Router()
  router
  .get('/', (req, res) => {
    Doctors.findAll({ include: [{ model: Hospitals, include: [Doctors], }], }).then((Doctors) => console.log(Doctors));
  })

and my main index

'use strict'

const express       = require('express'),
      path          = require('path'),
      bodyParser    = require('body-parser'),
      Sequelize     = require('sequelize'),
      port          = process.env.PORT || 3000,
      app           = express(),
      connection    = new Sequelize('Matadero', 'root', 'root');

app.use(bodyParser.json())
app.use(bodyParser.urlencoded({extended:true}))

app.set('view engine', 'ejs')
app.set('views', path.resolve(__dirname, 'client', 'views'))

app.use(express.static(path.resolve(__dirname, 'client')))

app.get('/', (req, res) =>  {
  res.render('admin/index.ejs')
})

const onListening = () => console.log(`Successful connection at port: ${port}`)



require('./models')(connection)

app.listen(port, onListening)

const patients   = require('./controllers/patients'),
      personal    = require('./controllers/personal')


app.use('/api/patients', patients)
app.use('/api/personal', personal)

The error i'm getting: Unhandled rejection Error: hospitals is not associated to doctors!

Answer 1

You must define your relationship on both models. Also when you use belongsTo, you are saying that a doctor belongs to a hospital, so the foreignKey is defined on the doctor, the foreign key in this case is the id of the hospital. But you set the doctor foreignKey as the id of the hospital which won't work.

module.exports = function(sequelize, DataTypes) {
  var doctors = sequelize.define('doctors', {
    id: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    lastName: {
      type: DataTypes.STRING,
      allowNull: false
    },
    SSN: {
      type: DataTypes.STRING,
      allowNull: false
    },
    idData: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      references: { // I'd recommend using the associate functions instead of creating references on the property, only causes confusion from my experience.
        model: 'data',
        key: 'id'
      }
    },
    speciality: {
      type: DataTypes.ENUM('Doctor','Geriatrician'),
      allowNull: false
    },
    // This is the foreignKey property which is referenced in the associate function on BOTH models.
    idHospital: {
      type: DataTypes.INTEGER(11),
      allowNull: false // Using allowNull makes this relationship required, on your model, a doctor can't exist without a hospital.
    },
  }, {
    tableName: 'doctor',
    freezeTableName: true,
    classMethods: {
      associate: models =>  {
        doctors.belongsTo(models.hospitals, {
          foreignKey: "idHospital"
        })
      }
    }
  });

  return doctors;
};

module.exports = function(sequelize, DataTypes) {
  var hospital = sequelize.define('hospitals', {
    id: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    idData: {
      type: DataTypes.INTEGER(11),
      allowNull: false,
      references: {
        model: 'data',
        key: 'id'
      }
    },
  }, {
    tableName: 'hospitals',
    freezeTableName: true,
    classMethods: {
        associate: models => {
            hospital.hasMany(models.doctors, {
                foreignKey: 'idHospital'
            }
        }
    }
  });

  return hospital;
};

UPDATE

The file you use to load your models is missing a lot of code that is required to make sequelize work. You need to call the associate function on each model manually. There is a standard implementation that sequelize provides that I modified a bit to work with your code.

var fs = require("fs");
var path = require("path");
var Sequelize = require("sequelize");

module.exports = (connection) => {
    var models = {};

    // Here we read in all the model files in your models folder.
    fs
        // This assumes this file is in the same folder as all your
        // models. Then you can simply do require('./modelfoldername')
        // to get all your models.
        .readdirSync(__dirname)
        // We don't want to read this file if it's in the folder. This
        // assumes it's called index.js and removes any files with    
        // that name from the array.
        .filter(function (file) {
            return (file.indexOf(".") !== 0) && (file !== "index.js");
        })
        // Go through each file and import it into sequelize.
        .forEach(function (file) {
            var model = connection.import(path.join(__dirname, file));
            models[model.name] = model;
        });

    // For each model we run the associate function on it so that sequelize
    // knows that they are associated.
    Object.keys(models).forEach(function (modelName) {
        if ("associate" in models[modelName]) {
            models[modelName].associate(models);
        }
    });

    models.connection = connection;
    models.Sequelize = Sequelize;

    return models
}

Answer 2

Did you try this?

Doctors.findAll({ include: Hospitals, });

To fetch medicos as well:

Doctors.findAll({ include: [{ model: Hospitals, include: [Medicos], }], });