Sails: Is there a way for sails.js to select fields in SQL queries?

[bigradish]

I'm new to sails.js. It seems that there is no way for sails.js to make a SQL query for select some fields/columns of a table. The queries seem all "select * ". Thank you.

[reecelewellen]

I believe it would be Model.query('custom SQL')

That said if it doesn't work underscores _…pluck() method is fantastic for
this

[particlebanana]

Ya it's currently not an option. Waterline which is the ORM that Sails uses is still pretty new and just starting to get the features it needs. We are working it on though!

Currently I'm pretty deep into associations and I may be able to tack on select as part of that. No promises but I will add it to the list.

For now the solution as @reecelewellen said is to use a custom SQL query if you want it. If you are just looking to limit the attributes returned you can override the toJSON() method of a model to only send back the attributes you want. This will work with the Blueprints if you are not writing custom controllers.

Example SQL Query:

Mode.query('SELECT id, name, email FROM user WHERE id = 1', function(err, users) {
    if(err) res.json({ error: err.message }, 400);
    res.json(users);
});

Example override toJSON

// Your Model
module.exports = {
    attributes: {
        // some attributes here
        name: 'string',
        email: 'string',
        password: 'string',

        // Override .toJSON instance method
        toJSON: function() {
            var obj = this.toObject();
            delete obj.password;
            return obj;
       }
    }
};

[bigradish]

@particlebanana Thank you so much. I think you can refer to laravel (http://laravel.com/) -- a very expressive PHP app framework -- to make sails.js better.

[amonaco]

Does Model.query() take any measure against SQL injection attacks? Is there a way to 'prepare' the query? Thanks!

[mikermcneil]

Hey @amonaco, here's the mysql implementation of .query: https://github.com/balderdashy/sails-mysql/blob/master/lib/adapter.js#L66

[johnlonganecker]

I just found a way to make this happen with sails ORM. I am using the 2nd parameter of Model.find . I did not see this in the documentation so I do not know if this is supported or a long term solution. Maybe @mikermcneil or @particlebanana can answer this.

Model.find({field: 'value'}, {fields: ['id', 'name']})

If you set fields to false it simulates a SELECT *

Model.find({field: 'value'}, {fields: false})

A complete example:

Model.find({field: 'value'}, {fields: ['id', 'name']})
    .paginate({page: 1}, {limit: 10)
    .exec(function(err, results) {
        if(err) {
            res.badRequest('reason');
        }
        res.jsonx(results);
    });

[mgenev]

@johnlonganecker neat! I'm going to test with mongo

[cjonagam]

Model.find({field: 'value'}, {fields: ['id', 'name']})
    .paginate({page: 1}, {limit: 10)
    .exec(function(err, results) {
        if(err) {
            res.badRequest('reason');
        }
        res.jsonx(results);
    });

The query above works only if we specify at least one select criteria (in where clause) for empty condition this is not working, any idea how to fix this ?

[sahanDissanayake]

@mikermcneil @particlebanana @sgress454 Any thoughts on this suggested function to limit the number of fields returned ? :thought_balloon:
Thanks

[mllanes]

Any update about how to do this using promises?

[JemiloII]

just for anyone who stumbles upon this, fields no longer works. use select instead.

Model.find({field: 'value'}, {select: ['id', 'name']})
    .paginate({page: 1}, {limit: 10})
    .exec(function(err, results) {
        if(err) {
            res.badRequest('reason');
        }
        res.json(results);
    });

[prasad-1210]

I'm still not able to get results as expected.
Test 1:
User.find({},{select: ['id', 'name']}).paginate({page: 1}, {limit: 10}).exec(function(err, data){console.log(data.length,data[0])});

Result:
All records with all keys

Test 2:
User.find({id: '552e5be95c0d53a5703cf315'},{select: ['id', 'name']}).paginate({page: 1}, {limit: 10}).exec(function(err, data){console.log(data.length,data[0])});

Result:
One record matching 'id' with all keys.

I'm using Sails.js 0.11.0 and mongodb.

Any suggestions?

[erick2014]

I have tried this approach:
Model.find({field: 'value'}, {fields: ['id', 'name']})
worked for me, but the query is taking 2,53 seconds to bring the data from remote server, and I only have one record

[alexventuraio]

How to do something like above but adding the populate option and selecting field from different models?

User.find({ belongs_to: user.id }, {fields: ['email', 'permission_level', 'signin_count']})
    .populate('person_id')
        .exec(function (err, results) {
            if(err) {
                res.badRequest('reason');
            }
            res.jsonx(results);
        });

I'm using Sails v0.11.2 and it still working javascript User.find({ belongs_to: user.id }, {fields: ['email', 'permission_level', 'signin_count']})

But I need to select fields from both models in a one-to-one relationship.
Any help?

[shadeven]

@particlebanana @mikermcneil Does sails custom query support joining multiple tables like the example below? Thanks in advance.

Model.query('SELECT * FROM user INNER JOIN pet on user.id = pet.user_id WHERE user.id = '1'', function(err, users) {
    if(err) res.json({ error: err.message }, 400);
    res.json(users);
});

[particlebanana]

@shadeven yup!

[dejesus2010]

Why is the id field included in these select fields limiter option? I'm using the select option to limit the fields returned in the find query, but it always returns the id field associated with the documents returned even though this field is not included in the array of fields selected to be returned in the result. I can't seem to return just the single field I want without the id field. I am using the sails-mongo db adapter. My query is as follows:

MyModel.find({where: { 
   fromPerson: userId,
   type: 'follow' } 
}, { select: ['to'] }).exec(function(err, results) {
 console.log(results);
}

The results printed are:

[
 {
   "to":"56d23af246722103009f4069"
   "id":"56e0f92030d2bff620d29275"
 },
 {
   "to":"56d6adeff6f8a697cbadf966"
   "id":"56e0fa4a30d2bff620d29276"
 },
]

Why is the id included and is there a way to remove it?

[irlnathan]

Howdy @dejesus2010, StackOverflow would be a better platform for this question. If, however, you think this is an issue, please open a new issue. Thanks!

[dejesus2010]

@irlnathan I'll try SO. Thanks.