mysql - Sequelize JS - 如何使用组合键进行多个连接查询？

Question

我是 Sequelize 的新手，为了一个简单的查询我已经浪费了几个小时。 Sequelize 的联想让我头晕@@。

所以我想运行这个查询

SELECT a.first_name, a.last_name, b.host_id FROM RoomDetails a
          LEFT JOIN Rooms b ON b.host_id = a.chat_id
          LEFT JOIN States c ON c.id = b.state_id
          WHERE c.bot='$bot_name' AND c.chat_id='$chatid';

我不知道如何以 Sequelize 格式实现这种方式。这是我的模型:

states.js

'use strict';
module.exports = function(sequelize, DataTypes) {
  var states = sequelize.define('states', {
    botName: DataTypes.STRING,
    chatId: DataTypes.INTEGER,
    state: DataTypes.STRING,
    turnId: DataTypes.INTEGER,
    turnName: DataTypes.STRING
  }, {
    classMethods: {
      associate: function(models) {
        states.hasOne(models.rooms, {
          as: 'rooms',
          foreignKey: {
            name: 'stateId'
          },
          foreignKeyConstraint: true
        });
        // associations can be defined here
      }
    }
  });
  return states;
};

rooms.js

'use strict';
module.exports = function(sequelize, DataTypes) {
  var rooms = sequelize.define('rooms', {
    stateId: DataTypes.INTEGER,
    hostId: DataTypes.INTEGER,
    maxLetter: DataTypes.INTEGER
  }, {
    classMethods: {
      associate: function(models) {
        rooms.belongsTo(models.states, {
          as: 'states',
          foreignKey: {
            name: 'stateId'
          },
          foreignKeyConstraint: true
        });
        rooms.hasMany(models.roomdetails, {
          as: 'roomdetails',
          foreignKey: {
            name: 'roomId'
          },
          foreignKeyConstraint: true
        });
        rooms.belongsToMany(models.roomdetails, {
          as: 'roomdetailschatid',
          foreignKey: {
            name: 'hostId'
          },
          foreignKeyConstraint: true
        });
        // associations can be defined here
      }
    }
  });
  return rooms;
};

roomdetails.js

'use strict';
module.exports = function(sequelize, DataTypes) {
  var roomdetails = sequelize.define('roomdetails', {
    roomId: DataTypes.INTEGER,
    chatId: DataTypes.INTEGER,
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING
  }, {
    classMethods: {
      associate: function(models) {
        roomdetails.belongsTo(models.rooms, {
          as: 'rooms',
          foreignKey: {
            name: 'roomId'
          },
          foreignKeyConstraint: true
        });
        roomdetails.belongsTo(models.rooms, {
          as: 'roomshostid',
          foreignKey: {
            name: 'chatId'
          },
          foreignKeyConstraint: true
        });
        // associations can be defined here
      }
    }
  });
  return roomdetails;
};

这是我迄今为止一直在尝试的

module.exports.getHostInfo = function(res) {
    return models.roomdetails.findOne({
        include: {
            required: true,
            model: models.rooms,
            as: 'rooms',
            include: {
                required: true,
                model: models.states,
                as: 'states',
                where: {
                    botName: { $eq: general.botName },
                    chatId: { $eq: res.chat.id },
                    state: { $ne: general.FINISHED}
                }
            }
        }
    })
    .then(function (response) {
        console.log(response);
        return response;
    })
    .catch(function (error) {
        console.log('error getTotalPlayer', error);
    });
}

它总是返回错误，例如“字段列表”中未知的列“rooms.roomdetailId”

有人可以帮助我吗？任何帮助将不胜感激，谢谢

Answer 1

正如错误所示，Sequelize 无法找到使用驼峰命名法的列，而数据库中的列是使用 Snake_Case 保存的。尝试使用snake_case定义foreignKeys:

    rooms.hasMany(models.roomdetails, {
      as: 'roomdetails',
      foreignKey: 'room_id',
      foreignKeyConstraint: true
    });