ORM watermark to insert when re-updating a key

Question

I have a user_address table and it has fields like

attributes: {
  user_id: 'integer',
  address: 'string' //etc.
}

I am currently doing this to insert a new entry, but if it exists for this user, update it:

UserAddress
  .query(
    'INSERT INTO user_address (user_id, address) VALUES (?, ?) ' +
      'ON DUPLICATE KEY UPDATE address=VALUES(address);',
    params,
    function(err) {
       //error handling logic if err exists
    }

Is there a way to use Waterline ORM instead of direct SQL queries to achieve the same? I do not want to make two requests because it is inefficient and difficult to maintain.

Answer 1

Create your own model method that does what you want using raw SQL's Waterline isntead queries. You will make two requests, but with Waterline syntax.

( , , ):

var Q = require('q');

module.exports = {
  attributes: {
    user_id: 'integer',
    address: 'string',
    updateOrCreate: function (user_id, address) {
      var deferred = Q.defer();

      UserAddress.findOne().where({user_id: user_id}).then(function (ua) {
        if (ua) {
          // UserAddress exists. Update.
          ua.address = address;
          ua.save(function (err) {deferred.resolve();});
        } else {
          // UserAddress does not exist. Create.
          UserAddress.create({user_id: user_id, address: address}).done(function (e, ua) {deferred.resolve();});
        }
      }).fail(function (err) {deferred.reject()});

    return deferred.promise;
  }
};

Answer 2

. , .

, , :

module.exports = {
  attributes: {
    user_id: 'integer',
    address: 'string'
  },
  updateOrCreate: function (user_id, address) {
    return UserAddress.findOne().where({user_id: user_id}).then(function (ua) {
      if (ua) {
        return UserAddress.update({user_id: user_id}, {address: address});
      } else {
        // UserAddress does not exist. Create.
        return UserAddress.create({user_id: user_id, address: address});
      }
    });
  }
}

, :

UserAddress.updateOrCreate(id, address).then(function(ua) {
   // ... success logic here
}).catch(function(e) {
   // ... error handling here
});

Answer 3

@Eugene , 2 : findOne + update create. , , , , update. :

module.exports = {
  attributes: {
    user_id: 'integer',
    address: 'string'
  },
  updateOrCreate: function (user_id, address) {
    return UserAddress.update({user_id: user_id}, {address: address})
    .then(function(ua){
      if(ua.length === 0){
        // No records updated, UserAddress does not exist. Create.
        return UserAddress.create({user_id: user_id, address: address});
      }
    });
  }
}

, .updateOrCreate : # 790