Selective specific columns in SailsJS via API

Question

I want to select certain columns be returned back from sailsjs / waterline / API

Given the search parameters:

var searchParams = {
"select":["ClientTypeID"]
, "where": {
"or" :
[{"ClientType": {"contains": "MAINT"}}]
}};

I make API call :

/api/reference/client_type?select=ClientTypeID&where=%7B%22or%22:%5B%7B%22ClientType%22:%7B%22contains%22:%22MAINT%22%7D%7D%5D%7D

based on

Select specific fields from database

I believe I am making the correct query, however, the JSON object that is returned has the all the columns of entity vs the 1 I wish to request ClientTypeID?

score 2 · Answer 1 · answered Aug 30 '15 at 16:37

This is known issue but I've found workaround. You can override default find blueprint with your own. You need to create api/blueprints/find.js file with content:

var _ = require('lodash');
var Promise = require('bluebird');
var actionUtil = require('sails/lib/hooks/blueprints/actionUtil');

var takeAliases = _.partial(_.pluck, _, 'alias');
var populateAliases = function (model, alias) {
  return model.populate(alias);
};

module.exports = function (req, res) {
  _.set(req.options, 'criteria.blacklist', ['limit', 'skip', 'sort', 'populate', 'fields']);

  var fields = req.param('fields') ? req.param('fields').replace(/ /g, '').split(',') : [];
  var populate = req.param('populate') ? req.param('populate').replace(/ /g, '').split(',') : [];
  var Model = actionUtil.parseModel(req);
  var where = actionUtil.parseCriteria(req);
  var limit = actionUtil.parseLimit(req);
  var skip = actionUtil.parseSkip(req);
  var sort = actionUtil.parseSort(req);
  var findQuery = _.reduce(_.intersection(populate, takeAliases(Model.associations)), populateAliases, Model.find().where(where).limit(limit).skip(skip).sort(sort));
  var countQuery = Model.count(where);

  Promise.all([findQuery, countQuery])
    .spread(function (_records, _count) {
      var records = fields.length > 0 ? _.map(_records, _.partial(_.pick, _, fields)) : _records;
      return [records, null, null, {
        criteria: where,
        limit: limit,
        start: skip,
        end: skip + limit,
        total: _count
      }];
    })
    .spread(res.ok)
    .catch(res.serverError);
};

This is general blueprint I'm using in all of my projects. For all fixed blueprints you can take a look here - https://github.com/ghaiklor/generator-sails-rest-api/tree/dev/generators/app/templates/api/blueprints

Yes, thanks much. I initially had issues running your example, I reduced the problem down to my lodash version was old. I need version 3+. Looking through stackover flow / git repository seems like they will implement this similar feature in v11 via pick / select. Based on my reading and understanding the code you have given will filter columns appropriately when sending to client, however, underlying query and model is fully populated (ie it does not reduce number of columns in 'sql' query). I guess that makes sense vs partially filled model which would be 'unstable.' Thanks again. — 4T2G, Aug 31 '15 at 01:42
yeah, you're right. But it's a quick workaround that works fine :) — Eugene Obrezkov, Aug 31 '15 at 08:02

Selective specific columns in SailsJS via API

1 Answers1