node.js - Sails JS Waterline join of multiple models

Question

Hi i'm trying to join multiple tables with populate method, i googled and couldn't find efficient way of doing it, i do not want to query db several times to build the result, is it possible to solve it with sails version "~0.10.0-rc7" i'm building quit big project with more then hundred of tables.

var co = {
    adapter: 'someMysqlServer',
    migrate:'safe',
    autoCreatedAt: false,
    autoUpdatedAt: false,
    autoPK:false,
    attributes:{
        id:{
            type:"int",
            primaryKey: true,
            autoIncrement: true
        },
        code:"string",
        priority :"int",
        co_group_c_id :"int",
        timezone_c_id :"int",
        lang_c_id :"int",
        currency_c_id :"int",
        name_used :"string",
        name_official :"string",
        co_tax_no :"int",
        co_vat_no :"int",
        co_vat_reg :"int",
        co_reg_record :"string",
        co_representative :"string",
        co_addresses:{
            collection: "co_address",
            via: "co_user_id"
        },
    }
};
module.exports = co;

var co_address = {
    adapter: 'someMysqlServer',
    migrate:'safe',
    autoCreatedAt: false,
    autoUpdatedAt: false,
    autoPK:false,
    attributes: {
        id:{
            type:"int",
            primaryKey: true,
            autoIncrement: true,
        },
        address_c_id:"int" ,
        address_street_first: "string",
        address_street_second: "int",
        address_street_third: "int",
        address_postalcode: "string",
        address_city: "string",
        co_user_id: {
            model: 'co_user'
        },
        co_id: {
            model: 'co'
        },
        co_address_opening_hours:{
            collection: "co_address_opening_hours",
            via: "co_address_id"
        },
    }
};
module.exports = co_address;

var co_address_opening_hours = {
    adapter: 'someMysqlServer',
    migrate:'safe',
    autoCreatedAt: false,
    autoUpdatedAt: false,
    autoPK:false,
    attributes:{
        id:{
            type:"int",
            primaryKey: true,
            autoIncrement: true
        },
        day_c_id: "int",
        time_from: "datetime",
        time_to :"datetime",
        co_address_id: {
            model: 'co_address'
        }
    }
};
module.exports = co_address_opening_hours;

//controller
    get_co:function(req,res){
        co.find()
            .populate("co_addresses")
            .populate("co_address_opening_hours")
            .exec(function(e, company) {
                if(e) console.log(e);
                console.log(company);
                res.json(company);
    })

Answer 1

In SailsJS 0.10+ you can use model associations to do database joins. You can read more about them here: http://sailsjs.com/documentation/concepts/models-and-orm/associations

Basically you first define an association in your model;

var someModel = {
  attributes: {
    name: {
      type: 'text'
    }
  }
};

var someOtherModel = {
  attributes: {
    name: {
      type: 'text'
    },
    associationProp: {
      model: 'someModel'
    }
  }
};

In the code above someOtherModel contains association (relation) to someModel. To do a join query you can use .populate() method. For example retrieve all someOtherModel entities and populate associative properties;

someOtherModel.find().populate('associationProp').exec(...);

For MySQL and PSQL adapters there's also .query() method available where you can write some hand written SQL queries to be executed (this also works in sails <0.10);

Model.query(<sql query>, <optional data>, callback);