Where condition in nested include in Sequelize

Question

I have a model with 3 entities, Documents, Employees and Managers. A Document belongs to and Employee and an Employee belongs to a Manager. My objective is to retrieve all the documents of a manager employees.

My piece of code is working

Document.findAll({
      include: [{
        model: models.Employee,
        required: true,
        as: 'employee',
        include: [{
          model: models.Manager,
          required: true,
          as: 'manager',
        }],
      }],

But I'm not really satisfied, I would like to have my where condition outside my include but when I try

where {
        'employee.manager.id': id
      }

an error is raised.

Is it possible to setup a where condition outside the includes ?

EDIT :

I changed my code to

Document.findAll({
where: {'$employee.manager.id$': id},
      include: [{
        model: models.Employee,
        required: true,
        as: 'employee',
        include: [{
          model: models.Manager,
          required: true,
          as: 'manager',
          where: { id: managerId },
        }],
      }],

and it's working.

Now, I would like to refine my model. Each document as a type (administrative, evaluation ...) and I would like to retrieve the most recent document for each type for each manager. I used an order by which is working fine and tried to use a group by which is not working

order: [ [ 'updatedAt', 'DESC' ]],
group: ['type'],

I get the following message : column \"Document.id\" must appear in the GROUP BY clause or be used in an aggregate function.

Any idea what I'm doing wrong ?

Answer 1

Yes, you can do that ,

Issue with current one :

where {
    'employee.manager.id': id // <--- Here 'employee.manager.id' cosidered as whole column name
}

Solution :

where {
    '$employee.manager.id$': id //<--- To make sequlize realize you need to place it b/w $$ ,
    //or
    sequelize.col('employee.manager.id') : id // <--- You can try this also
}