mysql - Sequelize 使用 LEFT JOIN 而不是嵌套的 INNER JOIN 进行多对多关联

Question

我在 Sequelize (MySQL) 中设置了三个模型——Book、Author 和 Book_author(连接表)。我设置了以下关联:

Book.belongsToMany(Author,
  {
    through: Book_author,
    foreignKey: 'book_id'
  }
);
Author.belongsToMany(Book,
  {
    through: Book_author,
    foreignKey: 'author_id'
  }
);

当我尝试对 Book 模型执行 findAll 查询时，如下所示...

Book.findAll({
    include: [
      { model: Author
      }
    ]
  }).then(bookList => {
    // do something with the bookList
});

...它会生成一个具有以下结构的原始 SQL 查询(为了简洁起见，我删除了各个属性):

SELECT book.*, author.* FROM book
LEFT JOIN (author
    INNER JOIN book_author
    ON author.author_id = book_author.author_id)
ON book.book_id = book_author.book_id;

但是，嵌套的 INNER JOIN 使我的查询变慢。单独测试原始 SQL 查询时也是如此，因此这不是 Sequelize 问题。为了缓解这种情况，我希望原始 SQL 查询只使用 LEFT JOIN，如下所示:

SELECT book.*, author.* FROM book
LEFT JOIN book_author
ON book.book_id = book_author.book_id
LEFT JOIN author
ON author.author_id = book_author.author_id;

我需要如何更改 Sequelize 模型和/或 findAll includes 属性才能获得此结果？我试过在 includes 属性中使用 required: false ，但这只会影响外部 JOIN，不会影响嵌套的 JOIN。有没有什么方法可以不使用 sequelize.query 来做到这一点？感谢您的帮助!

Answer 1

您可以使用两个 belongsTo 关联而不是 belongsToMany:

Book.hasMany(BookAuthor);
BookAuthor.belongsTo(Author);

const bookList = await Book.findAll({
    include: [{
        model: BookAuthor,
        required: false,
        include: [{
            model: Author,
            required: false 
        }]
    }]
});