如何在PostgreSQL中循环查询

Question

我需要一个对象数组，如下所示：

[
{
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...

我通过发送以下查询90次来做到这一点(这不是很聪明(：

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

如何通过PostgreSQL中的循环使其更智能？我觉得没有必要制定交货日期表，需要更多的逻辑来维护。

Answer 1

您可以在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;