0

I have a server side script which is trying to update an already existing row with new data that is calculated in the script. When there isn't a row there it adds to the table fine.However when I try to update the row I get an error in my application that tells me that an item with that id already exists. Below is the code I am using for the script. Any tips would be much appreciated.

function insert(item, user, request) {
var table = tables.getTable('Reviews');
table.where({ 
   text: item.id
}).read({
   success: upsertItem
});

function upsertItem(existingItems) {
    if (existingItems.length == 0) {
        item.numReviews = 1;
        item.rating = item.reviews;
        request.execute();
    } else {
        item.id = existingItems[0].id;
    item.numReviews = existingItems[0].numReviews + 1;
    var average = existingItems[0].reviews / item.numReviews;
    item.reviews = existingItems[0].reviews + item.reviews;
    item.rating = average;
      table.update(item, {
        success: function(updatedItem) {
            request.respond(200, updatedItem)
        }
      });
    }
 }

}

Robert Kenny
  • 81
  • 10

1 Answers1

1

For your initial query, you want to query by the id field:

table.where({
    id: item.id
}).read({
    success: upsertItem
});

Edit: further clarification Your query object, {text: item.id} is effectively turned into the SQL query, select * from Reviews where text = item.id where item is the POST body. So your code sample is searching for Reviews where the text column has an id value in it. It doesn't find any, so the upsert() callback function if statement evaluates to true because existingItems is empty, and tries to insert the item by calling request.execute().

With the change I suggested, using {id: item.id}, becomes a query like select * from Reviews where id = item.id so it will search for the Review with a matching id value in the id column.

Eric Hedstrom
  • 1,627
  • 10
  • 13
  • Thanks for the reply Eric.Unfortunately that didn't do the trick.For some reason it does not perform the update.It trys to insert the already existing item instead of updating it. Any ideas on why it might be doing this? – Robert Kenny Mar 26 '15 at 10:25
  • I updated my answer with more of an explanation what's going on. Does this make more sense? – Eric Hedstrom Mar 27 '15 at 15:24
  • Thanks for that @Eric I got it in the end :) – Robert Kenny Mar 27 '15 at 19:26