Sequelize where with include

Question

I am using sequelize as my backend ORM. But I have a problem when I want "where" with a join table. The associations are good but I didn't know how to do for the "where".

This my code :

router.get('/id_presta_struct_unit/:id_presta_struct_unit', (req, res) => {
  models.structures.findAll({
    include: {
      required: false,
      model: models.structures_proposer_prestations,
      where: {
        id_presta_struct_unit: req.params.id_presta_struct_unit
      },
      include: {
        model : models.unites_facturation,
      }
    }
  }).then(data => {
    res.writeHead(200, {'Content-Type': 'application/json; charset=utf-8'});
    res.end(JSON.stringify(data));
  });
});

I got this request

SELECT * FROM structures AS structures LEFT OUTER JOIN structures_proposer_prestations AS structures_proposer_prestations ON structures.id_structure = structures_proposer_prestations.id_structure AND structures_proposer_prestations.id_presta_struct_unit = '1' LEFT OUTER JOIN unites_facturation AS structures_proposer_prestations.unites_facturation ON structures_proposer_prestations.id_unite = structures_proposer_prestations.unites_facturation.id_unite;

But i would like to get

SELECT * FROM structures AS structures LEFT OUTER JOIN structures_proposer_prestations AS structures_proposer_prestations ON structures.id_structure = structures_proposer_prestations.id_structure LEFT OUTER JOIN unites_facturation AS structures_proposer_prestations.unites_facturation ON structures_proposer_prestations.id_unite = structures_proposer_prestations.unites_facturation.id_unite WHERE structures_proposer_prestations.id_presta_struct_unit = '1';

I don't know what to do I didn't find a post with the same problem

Can anyone point me in the right direction?

Thank you in advance.

Edit:

The associations

models.structures_employer_ressources.hasMany(models.ressources, { foreignKey: 'id_ressource' });
models.ressources.belongsTo(models.structures_employer_ressources, { foreignKey: 'id_ressource' });

The model of ressources

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('ressources', {
    id_ressource: {
      type: DataTypes.STRING,
      allowNull: false,
      primaryKey: true
    }
 ........
  },{
    tableName: 'ressources',
    updatedAt: 'date_modification',
    createdAt: 'date_creation'
  });
};

And the model of structures_employer_ressources

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('structures_employer_ressources', {
    id_structure: {
      type: DataTypes.STRING,
      allowNull: false,
      primaryKey: true,
      references: {
        model :'structures',
        key: 'id_structure'
      }
    },
    id_ressource: {
      type: DataTypes.STRING,
      allowNull: false,
      primaryKey: true,
      references: {
        model :'ressources',
        key: 'id_ressource'
      }
    }
  },{
    tableName: 'structures_employer_ressources',
    updatedAt: 'date_modification',
    createdAt: 'date_creation'
  });
};

How are the models and associations defined? Please edit your question to include these, thanks. — cfogelberg
Thank you for your reply I edited my post with the informations requested — Konstantin Sidorenko
stackoverflow.com/questions/31258158/… github.com/sequelize/sequelize/issues/… — igneosaur

Anthony Roberts Anthony Roberts · Accepted Answer · 2016-09-16T03:28:49

If you feed an array into the where clause of the initial join, you can run a raw query against the joins.

Example:

 models.structures.findAll({
where:[["[structures_proposer_prestations].[id_presta_struct_unit] = " + req.params.id_presta_struct_unit, null]],
    include: {
      required: false,
      model: models.structures_proposer_prestations,
      where: {
        id_presta_struct_unit: req.params.id_presta_struct_unit
      },
      include: {
        model : models.unites_facturation,
      }
    }
  }

The array can also take in standard object syntax and will be combined by an AND. The null I am passing in is for paramaters, so It can definitely be optimised to take in the id as a paramater, just don't know the syntax off hand.

Sequelize where with include

1 Answers