How to bulk insert with a select subquery-CodePudding

I'm using Sequelize in my project but I'm facing some trouble to translate in simple query into Sequelize. In addition, I'm looking for a way to bulk insert this query.

The query:

INSERT INTO productstatistics (shopId, productId, average, min, max) 
VALUES (SELECT p."shopId", p."productId", avg(p."basicPrice") as average, min(p."basicPrice"), max(p."basicPrice") FROM productprice p 
WHERE p."productId" = 123 AND p."shopId" = 321)

For the context, I have 3 tables: Shops, Products and productPrices. Each products is present in each shop. And each combinaison product / shop, productPrices have a historic of all the prices in that shop.

Here, I'm trying to create a new table productstatistics with some statistics from those 3 tables.

The first thing I tried is to loop over all my shops, then over all my products and do the Sequelize raw query.

But in my case, this is more than 3 000 000 records. I can do that in async but It takes forever, or doing in with Promise.all() but then I reach a :

JavaScript heap out of memory

How to use Sequelize in the best way to achieve my goal? Is there a way to use Sequelize to run the query describe earlier with all my products and all my shops?

CodePudding user response：

You either need to use Sequelize.query to execute this raw SQL query you indicated in the question OR execute SELECT part of the query as a raw SQL query (again using Sequelize.query) from productprice by chunks using limit and offset options and use bulkInsert of the Sequelize model.