How to query many to many relationship sequelize?

javascript, mysql, node.js, sequelize.js

Solution

Try it:

db.Outlet.findAll({
    include: [{
        model:db.Product, 
        attributes: ['id', 'name', 'nameKh'], 
        through: { where: { amount: 10 } }
    }]
})

Problem

Tables have many to many relationship, junction by an order table in between. Outlet --> Order <-- Product I want to get the list of Outlet for today Order. So here is a function to get all outlets: ``` db.Outlet.findAll({include: [ {model:db.Product, attributes: ['id', 'name', 'nameKh']} ]}).then(function(outlets){ return res.jsonp(outlets); }) ``` I got this result: I can only select with where Product Id by using this: ``` db.Outlet.findAll({include: [ {model:db.Product, attributes: ['id', 'name', 'nameKh'], where: {id: 2} ]}).then(function(outlets){ return res.jsonp(outlets); }) ``` How can I query by specific order amount, or today order date? Here are my models: Outlet: ``` var Outlet = sequelize.define('Outlet', { outletCode: DataTypes.STRING, outletName: DataTypes.STRING, outletNameKh: DataTypes.STRING, outletSubtype: DataTypes.STRING, perfectStoreType: DataTypes.STRING, address: DataTypes.STRING }, { associate: function(models){ Outlet.belongsToMany(models.Product, {through: models.Order}); Outlet.belongsTo(models.Distributor); // Outlet.hasMany(models.Order); } } ); ``` Product: ``` var Product = sequelize.define('Product', { inventoryCode: DataTypes.STRING, name: DataTypes.STRING, nameKh: DataTypes.STRING, monthlyCaseTarget: DataTypes.INTEGER, pieces: DataTypes.INTEGER, star: DataTypes.BOOLEAN, price: DataTypes.FLOAT, active: DataTypes.BOOLEAN }, { associate: function(models){ Product.belongsToMany(models.Outlet, {through: models.Order}); Product.belongsTo(models.Category); // Product.hasMany(models.Order); } } ); ``` Order: ``` var Order = sequelize.define('Order', { id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }, amount: DataTypes.INTEGER }, { associate: function(models){ Order.belongsTo(models.Outlet); Order.belongsTo(models.Product); Order.belongsTo(models.User); } } ); ```

Original source