pg-promise transaction response

Question

So I'm a little stuck here.

I have an object such as:

var lineItems = [
    {
      id: 1,
      quantity: 10
    },
    {
      id: 2,
      quantity: 15
    },
    {
      id: 4,
      quantity: 22
    }
]

now what I need to do is loop through this in a pg-promise SQL transaction to ensure that the row item in the database quantity for the associated id is >= the object items, so:

SELECT * FROM inventory WHERE id = $1 AND quantity >= $2, [1,10],
SELECT * FROM inventory WHERE id = $1 AND quantity >= $2, [2,15],
SELECT * FROM inventory WHERE id = $1 AND quantity >= $2, [4,22]

if I receive 3 results then the object is true and i'm able to insert the request with

INSERT INTO orders SET a = 1, b = 2, c = 3, items = lineItems::json etc RETURNING id;

and of course if i have less results than in the lineItems object then I don't bother with the INSERT statement, and simply return a 'item id: 4, quantity:22 doesn't have enough inventory to perform your request...

I was initially using:

db.tx(function(t)){
  return this.batch([
    checkInventory
  ])
    .then(function(inv){
      console.log( inv );
    });
})
.then(function(results){
  console.log( results );
})
.catch(function(error){
  console.log( error );
});

where checkInventory is the function I'm calling as

var checkInventory = function(){
    console.log( '|-------------------------------------|' );
    console.log( 'LINE ITEMS >> ', lineItems );
    var query = [], sql = ''; 
    for( var i = 0; i < lineItems.length; i++ ){
        sql += 'SELECT quantity FROM pim_inventory WHERE';
        sql += ' product_id = $1 AND quantity >= $2;';

        var pquery = [
            lineItems[i].id,
            lineItems[i].quantity
        ];

        query.push(this.one(sql, pquery));
    }

    console.log( 'invQUERY >> ', query );
    return query;
};

which is generating the following errors:

(node:44810) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 3): QueryResultError: Multiple rows were not expected.
(node:44810) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 4): error: syntax error at or near "product_id"
|-------------------------------------|
EVENTS >>  [ [ Promise {
      <rejected> QueryResultError: Multiple rows were not expected.
        at QueryResultError.Error (native)
        at new QueryResultError (/Users/directtap-dev/www/GitHub/v1.02/node_modules/pg-promise/lib/errors.js:13:22)
        at Query.callback (/Users/directtap-dev/www/GitHub/v1.02/node_modules/pg-promise/lib/index.js:629:38)
        at Query.handleReadyForQuery (/Users/directtap-dev/www/GitHub/v1.02/node_modules/pg/lib/query.js:89:10)
        at Connection.<anonymous> (/Users/directtap-dev/www/GitHub/v1.02/node_modules/pg/lib/client.js:163:19)
        at emitOne (events.js:101:20)
        at Connection.emit (events.js:188:7)
        at Socket.<anonymous> (/Users/directtap-dev/www/GitHub/v1.02/node_modules/pg/lib/connection.js:109:12)
        at emitOne (events.js:96:13)
        at Socket.emit (events.js:188:7) },
    Promise { <rejected> [Object] } ] ]

any ideas?

When you use this: `this.one(sql, pquery)` you create requests each expects exactly one row back, but at least one of them returns more than one row, hence the error `Multiple rows were not expected.`. See the method's API, it's all there. — vitaly-t, Feb 01 '17 at 22:59

vitaly-t · Answer 1 · 2018-01-30T09:39:20.947

When you ask too many questions at once, you are likely to get in multiple errors in the midst. Try solving one problem at a time.

In your code there are at least 5 problems, evidently...

Problem 1

When you use this: this.one(sql, pquery) you create requests each expects exactly one row back, but at least one of them returns more than one row, hence the error Multiple rows were not expected.. See the method's API, it's all there.

Problem 2

You use your function checkInventory to generate an array of promises, which is fine, but then instead of passing it into method batch directly, you do batch([checkInventory]), which results in batch([[promises]]), which doesn't get resolved because of it.

Problem 3

You use checkInventory rather than a function, which is invalid. You should call batch(checkInventory()).

Problem 4

You obviously do not show the complete code, as somewhere you do not chain the promises you create, which results in loose promises, and then Unhadled promise rejection in the end.

Problem 5

Some of the values you are passing for product_id are invalid, hence the server-level error about it.

Thanx Vitaly - I'm not understanding what you mean by 'instead of passing it into method ```batch``` because I didn't realize I was doing that incorrectly. can you give me an example of how to pass it into the method batch directly? — James Van Leuven, Feb 01 '17 at 23:18
You are supposed to pass in an array of promises, but instead you are passing in an array of arrays of promises. — vitaly-t, Feb 01 '17 at 23:35
yes thank you, i''m just realizing that :) i need to pass in: SELECT * FROM inventory a = $1 AND b >=$2, [ [1,10], [2,15], [4,22] ] — James Van Leuven, Feb 01 '17 at 23:38

pg-promise transaction response

1 Answers1