Sequelize find sum in Many to Many Relationship

Question

I need to build a many-to-many relationship between entities Product and Store(Wharehouse). So I managed to build a relationship with Sequlize like this.

Product.belongsToMany(Store, { through: 'product_store' });
Store.belongsToMany(Product, { through: 'product_store' });

product_store is the table that holds the relationship with Store and Product. When saving a product in a store in need some additional details like quantity so I added a field to the product_store

const Product_Store = sequelize.define("product_store", {
    id: {
        type: INTEGER,
        primaryKey: true,
        autoIncrement: true,
    },
    uid: {
        type: STRING,
        allowNull: false,
        unique: true,
    },
    ws_price: {
        type: DECIMAL(10, 2),
    },
    discount: {
        type: DECIMAL(10, 2),
    },
    bonus: {
        type: DECIMAL(10, 2),
    },
    quantity: {
        type: DECIMAL(10, 2),
    },
    total: {
        type: DECIMAL(10, 2),
    },
    active: {
        type: BOOLEAN,
        defaultValue: true,
    },
    createdAt: {
        type: DATE,
    },
    updatedAt: { type: DATE },
});

I managed to save the product in the store in the following way

await store.addProduct(product, {
                through: {
                    uid: "3462",
                    ws_price: 100,
                    discount: 2,
                    bonus: 1,
                    quantity: 2000,
                    total: 200000,
                }
            })

The values are saved properly and I could get products with store.getProdcuts(). But now I need to query the products and the sum of their quantities in all stores. I've started to query it with

let products = await Product.findAll({
                include: {
                    model: Store,
                    through: { attributes: ["ws_price", "discount", "bonus", "quantity", "total"] }
                }
            })

Which queries the product with store details(including the quantity) like this

[
  {
    "id": 1,
    "uid": "4323",
    "name": "Sunlight",
    "active": true,
    "createdAt": "2022-01-24T06:38:47.000Z",
    "updatedAt": "2022-01-24T06:38:47.000Z",
    "supplierId": null,
    "typeId": null,
    "unitId": null,
    "stores": [
      {
        "id": 1,
        "uid": "122333",
        "name": "Store1",
        "active": true,
        "createdAt": "2022-01-24T06:38:47.000Z",
        "updatedAt": "2022-01-24T06:38:47.000Z",
        "product_store": {
          "ws_price": "150.00",
          "discount": "2.00",
          "bonus": "1.00",
          "quantity": "2000.00",
          "total": "300000.00"
        }
      },
      {
        "id": 2,
        "uid": "34523",
        "name": "Store2",
        "active": true,
        "createdAt": "2022-01-24T06:38:47.000Z",
        "updatedAt": "2022-01-24T06:38:47.000Z",
        "product_store": {
          "ws_price": "300.00",
          "discount": "2.00",
          "bonus": "1.00",
          "quantity": "3000.00",
          "total": "300000.00"
        }
      }
    ]
  }
]

I only need to get the product details and the sum of product in all stores how can I achieve this?. I'm not familiar with using Count/Sum with relationships. Thanks in advance!

Answer 1

The most correct way is to use a subquery via Sequelize.literal like this:

let products = await Product.findAll({
   attributes: {
      include: [
        [Sequelize.literal('(SELECT sum(product_store.quantity) FROM product_store WHERE product_store.product_id=product.id)'), 'product_quantity']
      ]
   }
})