Sequelize find based on association

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

-------------Problems Reply------------

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

Don't your eye hurt physically, looking at otherwise correct c.hill's answer?

Author.sync({ force: true }).then(function() {
Book.sync({ force: true })}).then(function() {
Author.create({firstName: 'Test', lastName: 'Testerson'})}).then(function(author1) {
Author.create({firstName: 'The Invisible', lastName: 'Hand'})}).then(function(author2) {
Book.create({AuthorId: author1.id, title: 'A simple book'})}).then(function() {
Book.create({AuthorId: author1.id, title: 'Another book'})}).then(function() {
Book.create({AuthorId: author2.id, title: 'Some other book'})}).then(function() {
// This is the part you're after.
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')})
})

Category:node.js Views:0 Time:2013-09-16

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.170 (s). 11 q(s)