SailsJs - Joining multiple models with criteria on all joined models

Question

Below is my data model,

School:

• SchoolID
• Name
• Status

Game:

• SchoolID
• GameID
• Name
• Status

Participants:

• SchoolID
• GameID
• StudentID
• Name
• Status

I want to show "Participants" based on "Status" of "Participants", "School" and "Game". Is it possible to filter results while retrieving?

Query i want to execute is,

select *
from
    Participants
    inner join Game on Participants.GameID = Game.GameID
    inner join School on Game.SchoolID = School.SchoolID
where
    Participants.Status="Active"
    and Game.Status="Active"
    and School.Status="Active"

How can i achieve it using sailsjs model association?

Answer 1

There is no built-in way in Sails.js/Waterline of populating deep nested associations yet.

You need set models like that:

Game:

attributes:{
    SchoolID:{
        model: 'School'
    }
    // rest of attributes
}

Participants:

attributes:{
    SchoolID:{
        model: 'School'
    },
    GameID:{
        model: 'Game'
    }
    // rest of attributes
}

Than query:

Game.find({Status:"Active"})
    .populate("School",{
        where: {
            Status: "Active"
        }
    })
    .populate("Participants",{
        where: {
            Status: "Active"
        }
    }).exec(function (err, result){
        return result
    });

And now it's the tricky part. You will get Array with active games. Doesn't matter if they have active schools or participants. In result there will be 2 subarrays: Participants & School. If they are not empty thats your result.

[
    {
        SchoolID: [],
        GameID: [],
        Name: '',
        Status: ''
    },
    {
        SchoolID: [SchoolID: 1, Name: '', Status: 'Active'],
        GameID: [SchoolID: 1, GameID: 1, Name: '', Status: 'Active'],
        Name: '',
        Status: ''
    }
]

You can use lodash filter to cleanup the results.

Second solution much simpler is use .query()

You can just use your query that you wrote:

School.query('select * from Participants inner join Game on Participants.GameID=Game.GameID inner join School on Game.SchoolID=School.SchoolID where Participants.Status="Active" and Game.Status="Active" and School.Status="Active"', function(err, results) {

    return results;
});