javascriptarraysnode.jspostgresqlmassivejs

Run PostgreSQL UPDATE query for every object in array


Let's say I have a cart of grocery items and each item has a unique ID. When someone clicks "purchase", an array is sent with an object for each item in that cart. The cart varies so sometimes it might be 2 items and sometimes 6 items, etc.

Example:

[{id: 1, amt_purchased: 3}, {id: 2, amt_purchased: 4}]

I need my SQL table, "grocery items available", to update according to what was purchased.

For one grocery item, I would use the following:

UPDATE available 
SET amt_avail = amt_avail - 3
WHERE produce_id = 1

Since I have multiple items now, how can I get the query to run for each item that was purchased? Or as one massive query that will adapt according to how many items were purchased?

My project is Ionic/AngularJs and NodeJs, Express, MassiveJs.

Thanks guys! Still a noob so I'm having a hard time explaining what I need.


Solution

  • update available 
        set amt_avail = case 
             when produce_id = 1 then amt_avail - 3
             when produce_id = 2 then amt_avail - 4 
    end;
    

    You could use simple for loop to build such query

        var items = [{id: 1, amt_purchased: 3}, {id: 2, amt_purchased: 4}];
    
        function buildQuery(items) {
            var query = "update available set amt_avail = case ";
            for (var i = 0; i < items.length; i++) {
                var item = items[i];
                query += `when produce_id = ${item['id']} then amt_avail - ${item['amt_purchased']}`
            }
            return query += " end;";
    }