1

I have Books which have an author and an editor. I'd like to query for all books with a title like '%gone% and author.name like '%paul%' - I couldn't find documentation about doing this, but I've tried various formats based on what I've googled.

Attempt using a filter on includes:

var includes = [
   {"model": db.User, as: 'Author', through: {"where": {"name": { like: '%paul%'}}}}
];

var filter = { title: { like: '%gone%'}};

return Book.findAll({ include: includes, order: 'id desc', where: filter});

While the restriction on title works, the author name is ignored (no errors).

Variation without the 'through' (same result - doesn't affect resultset):

var includes = [
   {"model": db.User, as: 'Author', "where": {"name": { like: '%paul%' } } }
];

And how I initiatively thought it might work (causes error):

var filter = { title: { like: '%gone%'}, 'author.name' : { like : '%paul%' } };

Any clues or pointers to documentation to help me do this??

Thanks.

Model:

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

    name: {
      type: DataTypes.STRING
    },
...
    classMethods: {
      associate: function (models) {
        User.hasMany(Book, { as: 'Author', foreignKey: 'author_id' });
        User.hasMany(Book, { as: 'Editor', foreignKey: 'editor_id' });
      },
...
}


var Book = sequelize.define('Book', {
    title: {
      type: DataTypes.STRING,
      len: [20]
    },
...
    classMethods: {
      associate: function (models) {
        Book.belongsTo(User, { as: 'Author', foreignKey: 'author_id' });
        Book.belongsTo(User, { as: 'Editor', foreignKey: 'editor_id' });
      },
...
prule
  • 2,536
  • 31
  • 32

1 Answers1

1

I got it to work by removing the 'as' in the User associations to Book to:

User.hasMany(Book, { foreignKey: 'author_id' });
User.hasMany(Book, { foreignKey: 'editor_id' });

Then I can filter as expected with:

var includes = [
  {model: db.User, as: 'Author'},
  {model: db.User, as: 'Editor'}
];    
var filter = { title: { like: '%gone%'}, 'Author.name' : { like : '%paul%' } };
return Book.findAll({ include: includes, order: 'id desc', where: filter});

It helped to turn on logging of SQL statements as described here: How can I see the SQL generated by Sequelize.js?

Community
  • 1
  • 1
prule
  • 2,536
  • 31
  • 32