mysql - 如何在 Sequelize 中使用 JOIN LEFT？

Question

我有两个模型表，例如

Project = Sequelize.define('Project',{
  name: Sequelize.STRING
});

Task = Sequelize.define('Task',{
  name:Sequelize.STRING,
  status: Sequelize.BOOLEAN,
  deadline: Sequelize.DATE,
  from_name: Sequelize.STRING,
  ProjectId: Sequelize.INTEGER
});   

我需要从两个表中查找所有记录。如何在 Sequelize 中执行此查询？

SELECT * FROM mydb.Projects 
LEFT JOIN  mydb.Tasks ON Projects.id = Tasks.Project_id

我使用:

exports.list = function (req, res) {

  Project.hasMany(Task,{ foreignKey: { allowNull: false }, onDelete: 'CASCADE' });
  Project.all({
    include: [{
      model: Task,
      required:false,
      where:{ProjectId: Sequelize.col('Project.id')}
    }]
  })
  .then(function (projects) {
    res.render('TODOList', {title: 'TODO',  projects: projects || [] });
    console.log(projects);
  })
  .catch(function (err) {
    if (err) {
      res.render('TODOList', {title: 'TODO List'});
    }
  });

};

但我仅从项目获取记录，例如:

Project {
  dataValues: { 
    id: 5,
    name: 'pr2',
    createdAt: 2017-08-20T07:03:09.000Z,
    updatedAt: 2017-08-20T07:41:47.000Z,
    Tasks: [Object] 
  }
}

如何获取记录任务(Project.dataValues.Task)？

Answer 1

所以模型是(说出来:db/models.js):

const
  Project = Sequelize.define(
    'Project',
    {
      name: Sequelize.STRING
    }),
  Task = Sequelize.define(
    'Task', 
    {
      name: Sequelize.STRING,
      status: Sequelize.BOOLEAN,
      deadline: Sequelize.DATE,
      from_name: Sequelize.STRING,
      ProjectId: Sequelize.INTEGER
    });   

// relations must be defined once in model definition part (not like You do in every request
Project.hasMany(Task, {foreignKey: {allowNull: false}, onDelete: 'CASCADE'});
Task.belongsTo(Project);

module.exports = {Project, Task};

在你的路由器的处理程序中(说它routes/projects/list.js):

const Sequelize = require('sequelize');
const {Project, Task} = require('../../db/models');

module.exports.list = (req, res) => {
  const query = {
    include: [{
      model: Task,
      as: 'tasks',
      required: false
    }]
  };
  Project
    .all(query)
    .then(function (projects) {
      // it's not recommended, but You insist to get read of model instances and have normal array of objects
      projects = projects ? projects.map(project => project.toJSON()) : [];

      res.render(
        'TODOList', 
        {
          title: 'TODO',  
          projects
        });
        console.log(projects);
    })
    .catch(function (err) {
      console.error(err);
      res.render('TODOList', {title: 'TODO List'});
    });
};

但是由于sequelize返回sequelize模型实例的数组 - 有dataValues是正常的，不用担心，照常使用即可:

for(let project of projects) {
  console.log(project.tasks[0].name);
}