Sequelize find based on association

Question

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

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.

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

Answer 1

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