I am not sure how to correctly insert an array of objects into multiple relation tables in Postgres in one go. I am running into promise/async issues.
For the sake of reference, lets say that I am inserting a shopping cart, which consists of an array of objects into a purchases
, a purchase_items
, and a purchase_item_extras
table. I have the user_id
. The shopping cart array of objects is here below, along with the structure for each table of how I want the data to show up:
SHOPPING CART
[ { index: 0,
item_id: 2,
price: 0.01,
extras: [2],
extras_price: 1.00 },
{ index: 1,
item_id: 1,
price: 1.01,
extras: [1,2],
extras_price: 5.00 },
{ index: 2,
item_id: 2,
price: 2.01
} ]
purchases table
+----+-----------+------------+
| id | user_id | created_at |
+----+-----------+------------+
| 1 | asdf_1234 | 1-1-2001 |
+----+-----------+------------+
purchase_items table
+----+-------------+-------+--------------+------------+
| id | purchase_id | price | extras_price | created_at |
+----+-------------+-------+--------------+------------+
| 1 | 1 | 0.01 | 1.00 | 1-1-2001 |
+----+-------------+-------+--------------+------------+
| 2 | 1 | 1.01 | 5.00 | 1-1-2001 |
+----+-------------+-------+--------------+------------+
| 3 | 1 | 2.01 | 0.00 | 1-1-2001 |
+----+-------------+-------+--------------+------------+
purchase_item_extras table
+----+------------------+----------+------------+
| id | purchase_item_id | extra_id | created_at |
+----+------------------+----------+------------+
| 1 | 1 | 2 | 1-1-2001 |
+----+------------------+----------+------------+
| 2 | 2 | 1 | 1-1-2001 |
+----+------------------+----------+------------+
| 3 | 2 | 2 | 1-1-2001 |
+----+------------------+----------+------------+
So my question is, what is the most efficient method of inserting this into the database? And do I need to standardize my shopping cart so that each object in the array has the same format?
Right now, my strategy is inserting first into the purchases table
knex.insert({user_id: 'asdf_1234'}).into('purchases').then( function(purchases) { <next_step> })
and then having a forEach
loop run through each object in the array, with an additional nested forEach
loop to run through each of the addons that may exist within each item. I feel like this is nested hell.
via Thomas Gorczynski
No comments:
Post a Comment