Tuesday 30 May 2017

How to perform Sequelize join

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:

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.

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;



via LeDoc

No comments:

Post a Comment