javascript - 如何在 sequlize 中做一个简单的 LEFT JOIN？

Question

我想将 MySQL 查询“翻译”为 sequelize 查询，但我不断收到错误消息，说表之间没有关联。

这是 MySQL 查询：

` SELECT p.postId, p.postTitle, p.postContent, p.postOnThread, p.postOfAccount, t.threadName 
  FROM post as p 
  LEFT JOIN thread as t 
  ON p.postOnThread = t.threadId 
  WHERE postOnThread = ?
  ORDER by postId DESC`

错误信息：EagerLoadingError [SequelizeEagerLoadingError]: thread is not associated to post！

使用 sequelize 调用 db：

module.exports = function({ SQLiteDb }){
   return {
       getAllPosts: function(threadId, callback) {
           SQLiteDb.post.findAll({  
               where: { postOnThread: threadId },
               include:[{
                   model:  SQLiteDb.thread, as: 'thread',
                   required: false,
               }], 
               raw: true 
           })
           .then(posts => callback([], posts))
           .catch(error => console.log(error, " ERRPR")/*callback(['internalError'], null)*/)
       }, 
   }
}

数据库接口：

const Sequelize = require('sequelize')
const sequelize = new Sequelize('sqlite::memory:', 'defaultUsername', 'defaultPassword', {
   dialect: "sqlite",
   host: 'localhost',
   define: {
       freezeTableName: true
   }
})
sequelize.sync()

sequelize.authenticate()
  .then(() => console.log('Database Connected'))
  .catch(err => console.log('Error: ', err))

const db = {}

db.sequelize = sequelize
db.Sequelize = Sequelize

定义模型：

db.account = sequelize.define('account', {
   accountId: {
       type: Sequelize.INTEGER, 
       primaryKey: true, 
       autoIncrement: true
   },
   username: { 
       type: Sequelize.STRING
   },
   password: {
       type: Sequelize.STRING
   }
}, {
   timestamps: false
})

db.thread = sequelize.define('thread', {
   threadId: {
       type: Sequelize.INTEGER, 
       primaryKey: true, 
       autoIncrement: true
   },
   threadName: { 
       type: Sequelize.STRING
   },
   threadOfAccount: {
       type: Sequelize.INTEGER
   }
}, {
   timestamps: false
})

db.post = sequelize.define('post', {
   postId: {
       type: Sequelize.INTEGER, 
       primaryKey: true, 
       autoIncrement: true
   },
   postTitle: { 
       type: Sequelize.STRING
   },
   postContent:{
       type: Sequelize.TEXT,
       unique: true
   },
   postOnThread: {
       type: Sequelize.INTEGER
   },
   postOfAccount: {
       type: Sequelize.INTEGER
   }
}, {
   timestamps: false
})

定义关联：

db.post.associate = (models) => {
   post.belongsTo(models.thread, {
       foreignKey: 'postOnThread',
       as: 'thread',
   });
}

db.post.associate = (models) => {
   post.belongsTo(models.account, {
       foreignKey: 'postOfAccount',
       as: 'account',
   });
}

db.thread.associate = (models) => {
   thread.belongsTo(models.account, {
       foreignKey: 'threadOfAccount',
       as: 'account',
   });
}

db.thread.associate = (models) => {
   thread.hasMany(models.post, {
      foreignKey: 'postOnThread',
      as: 'post',
   });
}

module.exports = db

标签： javascriptnode.jssequelize.js