基于关联的 Sequelize 查找 [英] Sequelize find based on association

问题描述

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

How would I use Sequelize to find all people where a column in the relation satisfies a condition?

一个例子是查找作者姓氏为希区柯克"的所有书籍.图书模式包含与作者表的 hasOne 关系.

An example would be to find all Books whose author's last name is 'Hitchcock'. The book schema contains a hasOne relation with the Author's table.

我了解如何使用原始 SQL 查询完成此操作，但正在寻找另一种方法

I understand how this could be done with a raw SQL query, but looking for another approach

推荐答案

这里有一个工作示例，说明如何使用 Sequelize 获取具有特定内容的 Author 的所有 Books姓.它看起来比实际复杂得多，因为我正在定义模型，将它们关联起来，与数据库同步(以创建它们的表)，然后在这些新表中创建虚拟数据.查找代码中间的 findAll 以明确了解您要查找的内容.

Here's a working sample of how to user Sequelize to get all Books by an Author with a certain last name. It looks quite a bit more complicated than it is, because I am defining the Models, associating them, syncing with the database (to create their tables), and then creating dummy data in those new tables. Look for the findAll in the middle of the code to see specifically what you're after.

    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')
        });
  }

这篇关于基于关联的 Sequelize 查找的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！