Sequelize Nested Association with Two Tables

Question

I have a scenario where I am trying to query a parent table (document) with two associated tables (reference & user) that do not have a relationship to each other, but do have a relationship with the parent table. In SQL, this query would look like such and correctly outputs the data I am looking for:

select *
from `document`
left join `user`
on `document`.`user_id` = `user`.`user_id`
left join `reference`
on `document`.`reference_id` = `reference`.`reference_id`
where `user`.`organization_id` = 1;

However, associations that are nested have to relate in hierarchical order in order for the query to work. Since the nested associations are not related to each other I get an association error. How can I avoid this error? Would required: false have any influence on this?

models.Document.findAll({
  order: 'documentDate DESC',
  include: [{
    model: models.User,
    where: { organizationId: req.user.organizationId },
    include: [{
      model: models.Reference,
    }]
  }],
})

Error:

Unhandled rejection Error: reference is not associated to user!

Associations:

Document:

associate: function(db) {
  Document.belongsTo(db.User, {foreignKey: 'user_id'}),
    Document.belongsTo(db.Reference, { foreignKey: 'reference_id'});;
}

Reference:

associate: function(db){
  Reference.hasMany(db.Document, { foreignKey: 'reference_id' });
}

Should I just chain queries instead?

Answer 1

If you want to replicate your query (as closely as possibly) use the following query. Keep in mind that the where on the User include will only serve to remove matches on Document.user_id where the User.organization_id does not match, but the Document will still be returned. If you want to omit Documents where the User.organization_id does not match use required: true.

User <- Document -> Reference

models.Document.findAll({
  // this is an array of includes, don't nest them
  include: [{
    model: models.User,
    where: { organization_id: req.user.organization_id }, // <-- underscored HERE
    required: true, // <-- JOIN to only return Documents where there is a matching User
  },
  {
    model: models.Reference,
    required: false, // <-- LEFT JOIN, return rows even if there is no match
  }],
  order: [['document_date', 'DESC']], // <-- underscored HERE, plus use correct format
});