根据关联顺序查找

Question

如何使用Sequelize查找关系中的某个列满足条件的所有人？

一个示例是查找所有作者姓氏为“ Hitchcock”的图书。书籍架构包含与Author’s表的hasOne关系。

编辑：我了解如何使用原始SQL查询来完成，但是正在寻找另一种方法

Answer 1

这是一个有效的示例，说明如何让Sequelize
Books通过Author具有特定姓氏的来获取全部信息。它看起来比实际要复杂得多，因为我正在定义模型，将它们关联，与数据库同步（以创建其表），然后在这些新表中创建虚拟数据。findAll在代码中间查找，以明确了解您要执行的操作。

    module.exports = function(sequelize, DataTypes) {

    var Author = sequelize.define('Author', {

        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            autoIncrement: true,
            primaryKey: true
        },
        firstName: {
            type: DataTypes.STRING
        },
        lastName: {
            type: DataTypes.STRING
        }

    })

    var Book = sequelize.define('Book', {

        id: {
            type: DataTypes.INTEGER,
            allowNull: false,
            autoIncrement: true,
            primaryKey: true
        },
        title: {
            type: DataTypes.STRING
        }

    })

    var firstAuthor;
    var secondAuthor;

    Author.hasMany(Book)
    Book.belongsTo(Author)

    Author.sync({ force: true })
        .then(function() {
            return Book.sync({ force: true });
        })
        .then(function() {
            return Author.create({firstName: 'Test', lastName: 'Testerson'});
        })
        .then(function(author1) {
            firstAuthor=author1;
            return Author.create({firstName: 'The Invisible', lastName: 'Hand'});
        })
        .then(function(author2) {
            secondAuthor=author2
            return Book.create({AuthorId: firstAuthor.id, title: 'A simple book'});
        })
        .then(function() {
            return Book.create({AuthorId: firstAuthor.id, title: 'Another book'});
        })
        .then(function() {
            return Book.create({AuthorId: secondAuthor.id, title: 'Some other book'});
        })
        .then(function() {
            // This is the part you're after.
            return Book.findAll({
                where: {
                   'Authors.lastName': 'Testerson'
                },
                include: [
                    {model: Author, as: Author.tableName}
                ]
            });
        })
        .then(function(books) { 
            console.log('There are ' + books.length + ' books by Test Testerson')
        });
  }