Wednesday, 5 April 2017

Insert into multiple Postgres tables from array of objects, knex

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