3

I'm running sails 0.10.5 with postgresql support and I want to ask if there is any way to do a query to filter results by relations. For example:

http://api/documents?user.role=Admin

Or

http://api/documents?where={"user.role": "Admin"}

Or

http://api/documents?where={"user.role": {"like": "%Admin%"}}

Where a model Document has a belongsTo relation to User which has an attribute called role (string f.e.).

I wasn't able to do such query, Am I missing something?

Thank you!

Drakson
  • 41
  • 3

2 Answers2

1

I don't think that it's possible with SailsJS 0.10.5 for now. Actually I would like to do the same thing, so I decided to do implement a quick hack for this purpose.

Open file sails/lib/hooks/blueprints/actionUtil.js, edit method populateEach like below:

populateEach: function ( query, req ) {
    var DEFAULT_POPULATE_LIMIT = sails.config.blueprints.defaultLimit || 30;
    var _options = req.options;
    var aliasFilter = req.param('populate');
    var shouldPopulate = _options.populate;

    // Convert the string representation of the filter list to an Array. We
    // need this to provide flexibility in the request param. This way both
    // list string representations are supported:
    //   /model?populate=alias1,alias2,alias3
    //   /model?populate=[alias1,alias2,alias3]
    if (typeof aliasFilter === 'string') {
        aliasFilter = aliasFilter.replace(/\[|\]/g, '');
        aliasFilter = (aliasFilter) ? aliasFilter.split(',') : [];
    }

    return _(_options.associations).reduce(function populateEachAssociation (query, association) {        
        // If an alias filter was provided, override the blueprint config.
        if (aliasFilter) {
            shouldPopulate = _.contains(aliasFilter, association.alias);
        }

        // Only populate associations if a population filter has been supplied
        // with the request or if `populate` is set within the blueprint config.
        // Population filters will override any value stored in the config.
        //
        // Additionally, allow an object to be specified, where the key is the
        // name of the association attribute, and value is true/false
        // (true to populate, false to not)
        if (shouldPopulate) {
            // IMPORTANT NOTE: This is my trick. We should take advanced options from request parameter to make requests even more flexible
            var populationOptions = req.param('populate_' + association.alias);

            if (!populationOptions) {
                var populationLimit = _options['populate_' + association.alias+'_limit'] ||
                                      _options.populate_limit ||
                                      _options.limit ||
                                      DEFAULT_POPULATE_LIMIT;
                populationOptions = {limit: populationLimit};
            }

            return query.populate(association.alias, populationOptions);
        }
        else { 
            return query;
        }
    }, query);
},

Yay! Now your API can handle additional association filters like below:

# POST /api/documents
{
    "where" : {
        // Normal conditions
    }
    "populate_user": {
        // Advanced condition for association 'admin'
        "where" : {
            "role" : {
                 "like": "%Admin%"
            }
        },
        "limit" : 4    
     }
}

I hope that it helps. By the way I will find time to send a pull request of this improvement to SailsJS core tomorrow.

P/S: SailsJS core is quite well made. Probably core committers are just too busy to handle all feature requests. Let's contribute!

Ducky
  • 2,754
  • 16
  • 25
  • Hi Ducky, thanks for your answer, but I cant get it working. When I do what you suggest, I always get 'Details: error: column document.populate_user does not exist'. Furthermore, I was thinking in a more generic solution. One where you could navigate through relation tree using dot notation. I dont know if I'm explaining it well. =/ – Drakson Dec 19 '14 at 07:37
  • Could you give me more details about the way you make the request? This solution is working very well for me so far. Btw, if GET does not work please try POST instead ( jus in case) – Ducky Dec 19 '14 at 08:56
  • It has to be a GET request. My request: `http://api/documents?populate_user={where:{role:'Admin'}}` – Drakson Dec 19 '14 at 10:02
  • I don't think even default SailsJS blueprints support calling complicated GET request as yours though :-( – Ducky Dec 23 '14 at 14:43
  • There must be an easy way to do that! This functionality is very common on filtered tables where you want to order/filter by a relationship value. – Drakson Dec 23 '14 at 19:00
0

Seems the "population" is working fine in sails v0.12. I tested it for 1st level - associations and is working fine. But still didn't get a usable and reliable method to get only dominant model records that satisfy certain criteria in deep associations -> that was the original question, or even on 2nd or 3rd level deep. Writing custom controllers is the only way for now, or overhauling the client with more logic to handle such "inverse" search, this is how im doing it for now.

First i find the associated model that satisfy the needed criteria, then from this set i'm taking out "distinct" dominant record id's. It usually means at least one or two more requests to the server / level, because the final query has "OR" array of dominant model id's.

Furthermore making POST requests to some url to actually get data isn't such a great idea because it breaks the core of REST principles: https://spring.io/understanding/REST

cybercow
  • 427
  • 1
  • 4
  • 18