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?

例如查找作者姓氏为希区柯克"的所有书籍.book 模式包含与 Author 表的 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屋！