mysql - 如何执行Sequelize join

我有一个在 nodejs 中运行的 Express 应用程序，并使用 Sequelize 来处理 MySQL 数据库。在我的数据库逻辑中，我有一个从 ChatRoom 到 UserChatRoom 的一对多关系。当我尝试使用此代码执行连接时:

var userId = 1;

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

我在控制台上收到以下错误消息:

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

我想让连接查询工作，并希望得到帮助。我试图在 index.js 的末尾强制执行关联。此脚本在调用应用程序时加载此文件夹中的模型，并取自 Sequlize 网站。

注意:UserChatRoom.ucrIdChatRoom 是 UserChat.idChatRoom 的外键

楷模:

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;

}

和

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;

最佳答案

您必须以两种方式定义关系。 UserChatRoom 有一个 ChatRoom，ChatRoom 属于 UserChatRoom。此外，您的关系应该放在每个模型中，而不是放在索引中。

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;

}

关于mysql - 如何执行Sequelize join，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44262591/