How to perform Sequelize join

Question

I have an Express app running in nodejs and uses Sequelize to work with a MySQL database. In my db logic, I have a 1-to-Many relationship from ChatRoom to UserChatRoom. When I try to preform a join using this code:

var userId = 1;

db.ChatRoom.findAll({ include: [{
                model: db.UserChatRoom,
                where: {
                    ucrIdChatUser:userId 
                },
                required: false
            }]})

I get the following error message on the console:

Unhandled rejection Error: UserChatRoom is not associated to ChatRoom!
    at validateIncludedElement 

I would like to get the join query working and would appreciate help. I've tried to enforce associations at the end of index.js. This script loads the models in this folder when called the app starts and is taken from the Sequlize website.

Note: UserChatRoom.ucrIdChatRoom is a foreign key to UserChat.idChatRoom

Models:

module.exports = function(sequelize, DataType){
    var ChatRoom = sequelize.define("ChatRoom",{
        idChatUser: {
            type: DataType.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        crName: {
            type: DataType.STRING,
            allowNull: false
        },
        crDateInserted: {
            type: DataType.DATE
        },
        crDateUpdated: {
            type: DataType.DATE
        },
        crIsOpen: {
            type: DataType.INTEGER,
            defaultValue: 0
        }

    })

    return ChatRoom;

}

and

module.exports = function(sequelize, DataType){
    var UserChatRoom = sequelize.define("UserChatRoom",{
        idUserChatUser: {
            type: DataType.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        ucrIdChatUser: {
            type: DataType.INTEGER
        },
        ucrIdChatRoom: {
            type: DataType.INTEGER
        },
        ucrDateInserted: {
            type: DataType.DATE
        },
        ucrDateUpdated: {
            type: DataType.DATE
        }

    })

    return UserChatRoom;

}

index.js:

"use strict";

var fs        = require("fs");
var path      = require("path");
var Sequelize = require("sequelize");
var env       = process.env.NODE_ENV || "development";
var config    = require(path.join(__dirname, '..', 'config', 'config.json'))[env];

if (process.env.DATABASE_URL) {
  var sequelize = new Sequelize(process.env.DATABASE_URL,config);
} else {
  var sequelize = new Sequelize(config.database, config.username, config.password, config);
}

var db        = {};

fs
  .readdirSync(__dirname)
  .filter(function(file) {
    return (file.indexOf(".") !== 0) && (file !== "index.js")  && (file.slice(-3) === '.js');
  })
  .forEach(function(file) {
    var model = sequelize.import(path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(function(modelName) {
  if ("associate" in db[modelName]) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

db.UserChatRoom.hasOne(db.ChatUser);
db.UserChatRoom.hasOne(db.ChatRoom, { foreignKey: 'ucrIdChatRoom' , foreignKeyConstraint:true });

db.ChatUser.belongsTo(db.UserChatRoom, { foreignKey: 'ucrIdChatRoom' , foreignKeyConstraint:true});

db.ChatRoomMessage.hasOne(db.UserChatRoom, { foreignKey: 'crmIdUserChatRoom' , foreignKeyConstraint:true });

module.exports = db;

Answer 1

You have to define the relationship both ways. UserChatRoom hasOne ChatRoom, ChatRoom belongsTo UserChatRoom. Also, your relationships should be placed in each model, not in your index.

module.exports = function(sequelize, DataType){
    var ChatRoom = sequelize.define("ChatRoom",{
        idChatUser: {
            type: DataType.INTEGER,
            autoIncrement: true,
            primaryKey: true
        },
        //etc

    }, {
      classMethods: {
        //models/index.js calls this function
        associate: function(models) {
          ChatRoom.belongsTo(UserCharRoom, {foreignKey: 'something'});
        }
      }
    })

    return ChatRoom;

}