How to loop a query in PostgreSQL?-CodePudding

I need an array of objects as follows:

[
    {
        delivery_date: "2021-11-15",
        deliveries: [
            {
                product_name: "Big bag of vegetables",
                count: "1"
            },
            {
                product_name: "Eggs",
                count: "1"
            }
        ]
    },

// and so on for up to 90 days ahead...

I'm doing this by sending following query 90 times (which is not very smart):

        // Declare an array to store responses from looped query
        let deliveries = [];
        // Loop query until all deliveries within 90 days are returned
        for (let index = 0; index < 90; index  ) {
            const res = await pool.query(`
                SELECT
                    -- delivery date (converted to yyyy-mm-dd) given by...
                    TO_CHAR(
                        CURRENT_DATE   $1*delivery_interval -
                        MOD(CURRENT_DATE - start_date, delivery_interval), 'yyyy-mm-dd')
                    AS delivery_date,
                    product_name,
                    COUNT (product_name)
                FROM order_table
                INNER JOIN product_table
                ON product_table.id = order_table.product_id
                INNER JOIN customer_table
                ON customer_table.id = order_table.customer_id
                WHERE
                    -- no time-out on selected day
                    customer_id NOT IN (
                        SELECT customer_id
                        FROM time_out_table
                        WHERE ((CURRENT_DATE   $1*delivery_interval) BETWEEN start_time::date AND end_time))
                AND
                    -- delivery within 90 days (to prevent products with shorter delivery interval being replaced by products with longer interval)
                    $1*delivery_interval < 90
                GROUP BY
                    product_name,
                    -- delivery date (same expression as above)
                    CURRENT_DATE  
                    $1*delivery_interval -
                    MOD((CURRENT_DATE - start_date), delivery_interval)
                ORDER BY
                    product_name;
            `, [index]);
            // Add response to array
            res.rows.forEach(element => {
                deliveries.push(element);
            });
        }

How can I make this smarter with a loop in PostgreSQL? I feel a table for delivery dates is not necessary and would need more logic to be maintained.

CodePudding user response：

You can try this for the postgres QUERY :

 SELECT TO_CHAR( CURRENT_DATE   ind*delivery_interval
                - MOD(CURRENT_DATE - start_date, delivery_interval)
               , 'yyyy-mm-dd'
               ) AS delivery_date  -- delivery date (converted to yyyy-mm-dd) given by...
      , product_name
      , COUNT (product_name)
   FROM order_table
  INNER JOIN product_table
     ON product_table.id = order_table.product_id
  INNER JOIN customer_table
     ON customer_table.id = order_table.customer_i
  CROSS JOIN generate_series(0,89) AS ind
  WHERE customer_id NOT IN           -- no time-out on selected day
      ( SELECT customer_id
          FROM time_out_table
         WHERE (CURRENT_DATE   ind*delivery_interval) BETWEEN start_time::date AND end_time
      )
    AND ind*delivery_interval < 90  -- delivery within 90 days (to prevent products with shorter delivery interval being replaced by products with longer interval)                        
  GROUP BY product_name
      , CURRENT_DATE   ind*delivery_interval
        - MOD((CURRENT_DATE - start_date), delivery_interval)  -- delivery date (same expression as above)
  ORDER BY product_name;