Javascript Sails.js 模型 - 插入或更新记录

Sails.js model - insert or update records

提问by Critical Mash

Been trying out Sails.js and I'm writing an app that imports data from a third-party API and saves in into a MySQL table. Basically I'm trying to sync data over to my app for further analysis, updating my records or creating new records as needed.

一直在尝试 Sails.js，我正在编写一个应用程序，该应用程序从第三方 API 导入数据并保存到 MySQL 表中。基本上，我正在尝试将数据同步到我的应用程序以进行进一步分析、更新我的记录或根据需要创建新记录。

I've looked through Sails' API and I see methods to find, create and update records but no built-in method to insert/update records based on the situation. Did I overlook something, or will I need to implement this myself?

我查看了 Sails 的 API，我看到了查找、创建和更新记录的方法，但没有根据情况插入/更新记录的内置方法。我是否忽略了什么，还是我需要自己实施？

If I have to implement this myself, does anyone know of a good design pattern for insert/update?

如果我必须自己实现这个，有没有人知道插入/更新的好设计模式？

This is what I think it might look like…

这就是我认为它可能的样子……

_.each(importedRecords, function(record){
  MyModel.find({id: record.id}).exec(function findCB(err, found){
    if(found.length){
      MyModel.update(record.id, task).exec(function(err, updated){
        if(err) { //returns if an error has occured, ie id doesn't exist.
          console.log(err);
        } else {
          console.log('Updated MyModel record '+updated[0].name);
        }
      });
    }else{
       MyModel.create(record).exec(function(err, created){
         if(err) { //returns if an error has occured, ie invoice_id doesn't exist.
           console.log(err);
         } else {
           console.log('Created client record '+created.name);
         }
       });
     }
   });
 });

Am I headed in the right direction, or is there a more elegant solution?

我是朝着正确的方向前进，还是有更优雅的解决方案？

Also, I'm dealing with a lot of different models in this app, which would mean recreating this block of code across each of my models. Is there a way I can extend the base Model object to add this functionality for all models.

另外，我在这个应用程序中处理了很多不同的模型，这意味着在我的每个模型中重新创建这个代码块。有没有办法可以扩展基本模型对象来为所有模型添加此功能。

Thanks, John

谢谢，约翰

回答by Stas Arshanski

I have rewritten Critical Mash code, so its way less code, and more generic. Now you can call updateOrCreate the same way you call findOrCreate. And it looks like that:

我已经重写了 Critical Mash 代码，所以它的代码更少，而且更通用。现在您可以像调用 findOrCreate 一样调用 updateOrCreate。它看起来像这样：

module.exports.models = {
    updateOrCreate: function(criteria, values){
        var self = this; // reference for use by callbacks
        // If no values were specified, use criteria
        if (!values) values = criteria.where ? criteria.where : criteria;

        return this.findOne(criteria).then(function (result){
          if(result){
            return self.update(criteria, values);
          }else{
            return self.create(values);
          }
        });
    }
};

So that way you can write criteria the same way. no need to work on the key, and the code is so much simpler.

这样你就可以用同样的方式编写标准。无需在密钥上工作，代码也简单得多。

回答by Kenneth Benjamin

Sails 0.10 has findOrCreate(criteria, attributes, callback), see Sails Docs.

Sails 0.10 有findOrCreate(criteria, attributes, callback)，请参阅Sails 文档。

criteriais the search criteria for the "find" bit (same syntax as find()).

criteria是“查找”位的搜索条件（与 find() 的语法相同）。

attributesis the data used if it's not found for the "create" bit (same syntax as create()).

attributes是在找不到“创建”位（与 create() 语法相同）时使用的数据。

Here's an example:

下面是一个例子：

MyModel.findOrCreate({name:'Walter'},{name:'Jessie'}, function (err, record){ console.log('What\'s cookin\' '+record.name+'?');

MyModel.findOrCreate({name:'Walter'},{name:'Jessie'}, function (err, record){ console.log('What\'s cookin\' '+record.name+'?');

Also note that there are other composite query methods documented in the Waterline repository(see the testsfor examples) and Waterline documentation:

另请注意，Waterline 存储库（请参阅测试示例）和Waterline 文档中记录了其他复合查询方法：

Each of the following basic methods are available by default on a Collection instance:

• findOne
• find
• create
• update
• destroy
• count

In addition you also have the following helper methods:

• createEach
• findOrCreateEach *<-- Looks like what you need (hint use arrays of criteria / attributes) *
• findOrCreate
• findOneLike
• findLike
• startsWith
• endsWith
• contains

Based on your Collection attributes you also have dynamic finders. So given a name attribute the following queries will be available:

• findOneByName
• findOneByNameIn
• findOneByNameLike
• findByName
• findByNameIn
• findByNameLike
• countByName
• countByNameIn
• countByNameLike
• nameStartsWith
• nameEndsWith
• nameContains

默认情况下，Collection 实例上提供了以下每个基本方法：

• 找一个
• 找
• 创建
• 更新
• 破坏
• 数数

此外，您还有以下辅助方法：

• 创建每个
• findOrCreateEach *<-- 看起来像您需要的（提示使用条件/属性数组）*
• 查找或创建
• 找到一个赞
• 点赞
• 以。。开始
• 以。。结束
• 包含

根据您的收藏属性，您还有动态查找器。因此，给定 name 属性，以下查询将可用：

• findOneByName
• findOneByNameIn
• findOneByNameLike
• 按名称查找
• 按名称查找
• findByNameLike
• 按名称计数
• 按名称计数
• countByNameLike
• 名称起始于
• 名称结尾
• 名称包含

As for overlooking something, well it's out there but it's not in the main Sails documentation yet so the answer is yes and no, so don't sweat it :)

至于忽略某些东西，它就在那里，但它不在主要的 Sails 文档中，所以答案是肯定的，也不是，所以不要担心:)

回答by mdunisch

Your solution is right. There is no other way to do this with waterline (the ORM of sails.js). But several databases have functions for this case:

您的解决方案是正确的。没有其他方法可以使用水线（sails.js 的 ORM）来做到这一点。但是有几个数据库有这种情况的功能：

MySQL

MySQL

REPLACE INTO table SET id = 42, foo = 'bar';(with a primary or unique key. Pretty shitty if you use auto_increment ;-)

REPLACE INTO table SET id = 42, foo = 'bar';（带有主键或唯一键。如果您使用 auto_increment 就很糟糕；-）

In Waterline you can use the Model.query()-Functionto execute direct SQL (see: http://sailsjs.org/#/documentation/reference/waterline/models/query.html)

在 Waterline 中，您可以使用模型。query() -执行直接 SQL 的函数（参见：http: //sailsjs.org/#/documentation/reference/waterline/models/query.html）

MongoDB

MongoDB

db.collection.update(
  <query>,
  <update>,
  { upsert: true }
)

The upsert flag means: If you can't update it because you didn't find anything with the query create this element!

upsert 标志意味着：如果您无法更新它，因为您在查询中找不到任何内容，请创建此元素！

In Waterline you can use the Model.native()-Functionto execute direct mongoDB-Commands (see: http://sailsjs.org/#/documentation/reference/waterline/models/native.html)

在 Waterline 中，您可以使用模型。native()-执行直接 mongoDB-Commands 的函数（参见：http://sailsjs.org/#/documentation/reference/waterline/models/native.html ）

Conclusion

结论

You you need fast execution (and of corse if you have many many request) I would suggest to use the native/sql-functions. But in general I'm really a fan of the flexibility of an ORM-System and every time you use database-specific functions it's harder to handle.

您需要快速执行（如果您有很多请求，则需要使用 corse）我建议使用本机/sql 函数。但总的来说，我真的很喜欢 ORM 系统的灵活性，每次使用特定于数据库的函数时，它都很难处理。

回答by Critical Mash

Thanks user3351722, I prefer using the ORM system as well. I just tried implementing the above solution as a general Model method. (Based on Inherit attributes and lifecycle functions of Sails.js models).

感谢 user3351722，我也更喜欢使用 ORM 系统。我只是尝试将上述解决方案实现为通用模型方法。（基于Sails.js 模型的继承属性和生命周期函数）。

I edited config/models.js and added a new function insertOrUpdatethat takes the name of the index column, the data I want to insert or update and a callback function.

我编辑了 config/models.js 并添加了一个新函数insertOrUpdate，该函数采用索引列的名称、我想要插入或更新的数据以及一个回调函数。

module.exports.models = {
  insertOrUpdate: function(key, record, CB){
    var self = this; // reference for use by callbacks
    var where = {};
    where[key] = record[key]; // keys differ by model
    this.find(where).exec(function findCB(err, found){
      if(err){
        CB(err, false);
      }
      // did we find an existing record?
      if(found && found.length){
        self.update(record[key], record).exec(function(err, updated){
          if(err) { //returns if an error has occured, ie id doesn't exist.
            CB(err, false);
          } else {
            CB(false, found[0]);
          }
        });
      }else{
        self.create(record).exec(function(err, created){
          if(err) { //returns if an error has occured, ie invoice_id doesn't exist.
            CB(err, false);
          } else {
            CB(false, created);
          }
        });
      }
    });
  }
};

This would only work with tables/collections that have an index. I don't know how to introspect the key name from a model in waterline so I pass in into the field name as a string.

这仅适用于具有索引的表/集合。我不知道如何从水线中的模型中反省键名，所以我将字段名作为字符串传入。

Here's how you might use the method inside a controller…

以下是您如何在控制器中使用该方法......

_.each(clients, function(client){
  Client.insertOrUpdate('client_id', client, function(err, updated){
    if(err) { //returns if an error has occured, ie invoice_id doesn't exist.
      sails.log(err);
    } else {
      sails.log('insertOrUpdate client record ', updated.organization); //+updated[0].name
    }
  });
});

I've tried this method with three different models and so far, so good. They're all MySQL tables and the models all have a defined index. Your milage may very if you're using a different datastore.

我已经用三种不同的模型尝试了这种方法，到目前为止，非常好。它们都是 MySQL 表，模型都有一个定义的索引。如果您使用不同的数据存储，您的里程可能会很大。

If anyone sees a way to improve upon this, please let us know.

如果有人看到改进方法，请告诉我们。

回答by scott

This is how I do it: I extend the config/models.js to include the functionality and it checks to see if the adapter has the correct methods. You can call it as a promise or normally.

这就是我的做法：我扩展 config/models.js 以包含功能，并检查适配器是否具有正确的方法。您可以将其称为承诺或正常情况。

    var normalize = require('sails/node_modules/waterline/lib/waterline/utils/normalize');
    var hasOwnProperty = require('sails/node_modules/waterline/lib/waterline/utils/helpers').object.hasOwnProperty;
    var defer = require('sails/node_modules/waterline/lib/waterline/utils/defer');
    var noop = function() {};

module.exports.models = {

    /**
     * [updateOrCreate description]
     * @param  {[type]}   criteria [description]
     * @param  {[type]}   values   [description]
     * @param  {Function} cb       [description]
     * @return {[type]}            [description]
    */

    updateOrCreate: function (criteria, values, cb) {
        var self = this; 
        var deferred;

        // Normalize Arguments
        if(typeof cb !== 'function') {
           deferred = defer();
        }
        cb = cb || noop;

        criteria = normalize.criteria(criteria);

        if (criteria === false) {
            if(deferred) {
                deferred.resolve(null);
            }
            return cb(null, []);
        }
        else if(!criteria) {
            if(deferred) {
                deferred.reject(new Error('No criteria or id specified!'));
            }
            return cb(new Error('No criteria or id specified!'));
        }

        // Build Default Error Message
        var errFind = 'No find() method defined in adapter!';
        var errUpdate = 'No update() method defined in adapter!';
        var errCreate = 'No create() method defined in adapter!';

        // Find the connection to run this on
        if(!hasOwnProperty(self.adapter.dictionary, 'find')){
            if(deferred) {
                deferred.reject(errFind);
            }
            return cb(new Error(errFind));
        }
        if(!hasOwnProperty(self.adapter.dictionary, 'update')){ 
            if(deferred) {
                deferred.reject(errUpdate);
            }
            return cb(new Error(errUpdate));
        }
        if(!hasOwnProperty(self.adapter.dictionary, 'create')) {
            if(deferred) {
                deferred.reject(errCreate);
            }
            return cb(new Error(errCreate));
        }

        var connNameFind = self.adapter.dictionary.find;
        var adapterFind = self.adapter.connections[connNameFind]._adapter;

        var connNameUpdate = self.adapter.dictionary.update;
        var adapterUpdate = self.adapter.connections[connNameUpdate]._adapter;

        var connNameCreate = self.adapter.dictionary.create;
        var adapterCreate = self.adapter.connections[connNameCreate]._adapter;

        adapterFind.find(connNameFind, self.adapter.collection, criteria, normalize.callback(function before (err, results){

            if (err) {
                if(deferred) {
                    deferred.reject(err);
                }
                return cb(err);
            }

            if(results && results.length > 0){
                adapterUpdate.update(connNameUpdate, self.adapter.collection, criteria, values, normalize.callback(function afterwards (err, updatedRecords) {
                    if (err) {
                        if(deferred) {
                            deferred.reject(err);
                        }
                        return cb(err);
                    }
                    deferred.resolve(updatedRecords[0]);
                    return cb(null, updatedRecords[0]);
                }));
            }else{
                adapterCreate.create(connNameCreate, self.adapter.collection, values, normalize.callback(function afterwards (err, createdRecord) {
                    if (err) {
                        if(deferred) {
                            deferred.reject(err);
                        }
                        return cb(err);
                    }
                    deferred.resolve(createdRecord);
                    return cb(null, createdRecord);
                }));
            }
        }));

        if(deferred) {
            return deferred.promise;
        }
    }
}