CODEWITHSUNDEEP
mysqlnode.jsasynchronoussails.js
Jeffpowrs
Jeffpowrs

Reputation: 4550

Sails and MySQL are creating duplicate rows

I'm using sails with mysql and I'm having a problem with my asynchronous flow resulting in duplicate rows being created in my database.

I have a one-to-many relationship, in which a restaurant can have many reviews. In my review create action, I first look in the database to see if the restaurant that the new review will be associated with already exists. If it exists I grab the existing ID, otherwise I create an entry for the restaurant and then use the new ID as the foreign key to associate the review with the new restaurant.

The problem is when multiple reviews are submitted at once all for the same restaurant that doesn't exist in the database. Each review create will look in the database for the restaurant, it won't exist. So, each one create will create an entry for the restaurant. Therefore, duplicate entries will exist for the same restaurant.

I'm not sure what the best approach is for preventing this problem. Does anyone have any advice? It would be greatly appreciated. Thanks.

Here is my code:

create: function(req, res) {
//probably put this in a helper
var validAttributes = ["content", "foursquareId", "author"];
validAttributes.unshift(req.body);
var reviewParams = _.pick.apply(null, validAttributes);

async
.waterfall(
  [
    function(callback) {

      Venue
      .findOneByFoursquareId(reviewParams.foursquareId)
      .exec(function(err, existingVenue) {

        debugger;

        if (err) {
          console.log("Error: " + err);
          return res.send(500, "Error finding venue: " + err);
        }

        if (! existingVenue) {

          // The Venue Exists in the Database

          sails.venuesHelper
          .create(reviewParams.foursquareId, function(err, newVenue) {

            if (err) {
              console.log("Error creating venue: " + err);
              return res.send(500, "Error creating venue: " + err);
            }

            console.log('created : ', newVenue);
            reviewParams.venue = newVenue.id;

            callback(null);

          });

        }
        else {

          console.log('existed : ', existingVenue);

          reviewParams.venue = existingVenue.id;

          callback(null);

        }

      });

    },

    function(callback) {

      // The venue exists in the database.

      review.create(reviewParams)
      .exec(function(err, review) {

        if (err) {
          console.log("Error: " + err);
          return res.send(500, "review could not be saved: " + err);
        }

        console.log(review);

        callback(null, review);

      });

    },

  ],
  function(err, results) {

    return res.json(results);

  }
);

},

Upvotes: 0

Views: 256

Answers (1)

gpkamp
gpkamp

Reputation: 82

My approach is that I would make the fourSquareId a unique key, and then the 2nd insert won't be able to insert a duplicate record.

You can add a check on the insert, that upon error, tries again after a second or so, and if that fails again, then throw an exception.

Upvotes: 1

Related Questions