Sails.js Waterline query by association

Question

I'm developing and app with Sails.js and using Waterline orm for db. I'm developing functionality for users to do friend requests and other similar requests to each other. I have following URequest model for that:

    module.exports = {

    attributes: {
        owner: {
            model: 'Person'
        },

        people: {
            collection: 'Person'
        },

        answers: {
            collection: 'URequestAnswer'
        },

        action: {
            type: 'json'    //TODO: Consider alternative more schema consistent approach.
        }
    }
};

Basically owner is association to Person who made the request and people is one-to-many association to all Persons who the request is directed. So far fine.

Now I want to have a controller which returns all requests where certain user is involved in meaning all requests where user is either in owner field or in people. How I do query like "give me all rows where there is association to person P" ? In other words how I ca know which URequest models have association to a certain Person?

I tried something like this:

getRequests: function (req, res) {
    var personId = req.param('personId');
    URequest.find().where({
    or: [
        {people: [personId]},   //TODO: This is not correct
        {owner: personId}
        ]
}).populateAll().then(function(results) {
        res.json(results);
    });

},

So I know how to do the "or" part but how do I check if the personId is in people? I know I should somehow be able to look into join-table but I have no idea how and couldn't find much from Waterline docs relating to my situation. Also, I'm trying to keep this db-agnostic, though atm I'm using MongoDB but might use Postgres later.

Adam William Larson Adam William Larson · Accepted Answer · 2015-09-22T17:01:07

I have to be honest this is a tricky one, and, as far as I know what you are trying to do is not possible using Waterline so your options are to write a native query using query( ) if you are using a sql based adapter or native otherwise, or try doing some manual filtering. Manual filtering would depend on how large of a dataset you are dealing with.

My mind immediately goes to reworking your data model a bit, maybe instead of a collection you have a table that stores associations. Something like this:

module.exports = {

attributes: {
    owner: {
        model: 'URequest'
    },

    person: {
        model: 'Person'
    }
}

Using the sailsjs model methods (like beforeCreate) you could auto create these associations as needed.

Good Luck, I hope you get it working!

Sails.js Waterline query by association

1 Answers