CODEWITHSUNDEEP
javascriptmysqlnode.jssequelize.js
Vicheanak
Vicheanak

Reputation: 6694

How to query many to many relationship sequelize?

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:

enter image description here

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);
            }
        }
    );

Upvotes: 20

Views: 28030

Answers (2)

Md Shayon
Md Shayon

Reputation: 375

Similar case

Super many to many relatioship

User.belongsToMany(Profile, { through: Grant });
Profile.belongsToMany(User, { through: Grant });
User.hasMany(Grant);
Grant.belongsTo(User);
Profile.hasMany(Grant);
Grant.belongsTo(Profile);

This way, we can do all kinds of eager loading:

// All these work:
User.findAll({ include: Profile });
Profile.findAll({ include: User });
User.findAll({ include: Grant });
Profile.findAll({ include: Grant });
Grant.findAll({ include: User });
Grant.findAll({ include: Profile });

We can even perform all kinds of deeply nested includes:

User.findAll({
  include: [
    {
      model: Grant,
      include: [User, Profile]
    },
    {
      model: Profile,
      include: {
        model: User,
        include: {
          model: Grant,
          include: [User, Profile]
        }
      }
    }
  ]
});

From sequelize doc

Upvotes: 0

Yrysbek Tilekbekov
Yrysbek Tilekbekov

Reputation: 2775

Try it:

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

Upvotes: 24

Related Questions