Sails JS Waterline join of multiple models

express, node.js, sails.js, waterline

Solution

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);

Problem

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); }) ```

Original source