how to get record from multiple collection in sails.js

Question

I am newbie in sails.js , I am trying to create crud and login using sails js. I want to fetch record of loggedin user from multiple collection like user profile details and post done by login user. I dont know how to query from multiple collection like User, Dashboard(Consist of all post made by user after login). If someone has idea please share with me or guide me how to do this task.

Here is my Dashboardcontroller

require('../models/Dashboard.js');
var User = require('../models/User.js');
module.exports = {
     //View all record posted by login user from dashboard collection
    index: function(req, res ){
        //console.log(req.session.passport.user);
        var userId = req.session.passport.user;



    // here i want to get data from user collection and want to send to the view




        Dashboard.find({posted_by: {$in: [userId]}}).exec(function(err, result){
            if(err){
                sails.log("----Error----"+err);
            }else{
            /*  console.log(result);*/
                res.view('dashboard/index',{'result': result}); 
            }
        });
    },
    create: function(req, res){
        //view for new post entry
    //  var userId = req.session.user_detail._id;
    //  sails.log(req.session);
        res.view('dashboard/create');
    },
    newpost: function(req, res){
        // Post New record to dashboard collection
        var params = req.params.all();
        var userId = req.session.passport.user;

        Dashboard.create({ title: params.heading, description: params.message, posted_by: userId }).exec(function(error, post_created){
            if(error){

            }else{
                req.session.post_details = post_created;
                res.redirect('/dashboard');
            }
        });
    },
    removePost: function(req, res)
    {
        // remove record from collection
        var id = req.query.id;
        //var id = req.param("id", null);
        //console.log(id);
        //sails.log(id);
        Dashboard.findOne(id).exec(function(err, user) {
            user.destroy(function(err) {
            res.redirect( 'dashboard/index/');
                  // record has been removed
            });
        });
    },
    update: function(req, res){
        // Get dashboard collection record to update
        var id =req.query.id;
        console.log(id);
        Dashboard.findOne(id).exec( function(err, post){
        //  console.log(post);
        res.view('dashboard/update',{'post':post});
        });
    },
    edit: function (req, res) {
    // Update the record in dashboard collection
    var id = req.param("id",null);
   // var params = req.params.all()
    Dashboard.findOne(id).exec(function(err, dashboard) {
        dashboard.title = req.body.title;
        dashboard.description = req.body.description;
        //user.name = params;
        dashboard.save(function(err){
        if(err) {
            res.send("Error");
        }else {
           res.redirect('dashboard/index');
        }
      });
  });
  },
  profile: function(req, res){
        //view for new post entry
    //  var userId = req.session.user_detail._id;
    //  sails.log(req.session);
        res.view('dashboard/profile');
    },

 /* upload: function(req, res){
    if(req.method === 'GET')
        return res.json({ 'status':'Get Not Allowed'});
    console.log('status: Get Not Allowed');
    var userPhoto =req.file('userPhoto');
    console.log("*******User Photo***********");
    console.log(userPhoto);
    console.log('****************************');
    userPhoto.upload( function onUploadComplete (err, files){
        //file upload to /tmp/public folder

        if(err) return res.serverError(err);
        // if error return  and send 500 error with error
        console.log("*************** Uploaded file ************* ");
        console.log(files);
        var id = req.session.passport.user;
        //res.json({status: 200, file: files});
        User.findOne(id).exec( function (err, user){
            user.userName = req.body.userName;
            user.userPhoto = req.body.files;
            user.save(function (err){
                if(err) { console.log("Error updating profile");}
                res.redirect('dashboard/index');
            });

        });
    });
  }*/
};

Here is my Usermodel

  var bcrypt = require('bcrypt');

module.exports = {
    attributes: {
        username:{
            type: 'string'
        },
        userphoto: {
            type: 'string'            
        },
        email: {
            type: 'email',
            required: true,
            unique: true
        },
        password: {
            type: 'string',
            minLength: 6,
            required: true
        },
        toJSON: function() {
            var obj = this.toObject();
            delete obj.password;
            return obj;
        }
    },
    beforeCreate: function(user, cb) {
        bcrypt.genSalt(10, function(err, salt) {
            bcrypt.hash(user.password, salt, function(err, hash) {
                if (err) {
                    console.log(err);
                    cb(err);
                } else {
                    user.password = hash;
                    cb();
                }
            });
        });
    }
};

Here is my dashboard model

module.exports = {

  attributes: {
    title:{
         type:'string',
         //required:true,
         columnName:'title'
    },
    description: {
         type:'text',
         columnName:'description'
    },
    posted_by: {
        type: 'integer',
        columnName: 'posted_by'
    }

  }
};

Thanks in advance

Answer 1

Is the posted_by attribute a User id? If yes, you should use a OneToMany relation.

// In the Dashboard model
module.exports = {

  attributes: {
    // ...
    posted_by: {
      model:'User'
    }
  }

}

Define the relation in the User model too:

// In the User model
module.exports = {

  attributes: {
    // ...
    dashboards: {
      collection: 'Dashboard',
      via: 'posted_by'
    }
  }

}

Once you defined the relation, the Dashboards will be shown in your blueprints call to the User model.

If you don't want to use blueprints, you can use the populate() method.

User.findOne({id: userId}).populate('dashboards').exec(function(err, user) {
  // Here, user.dashboards is populated
  res.view('dashboard/index',{'user': user}); 
})

I would rename posted_by as user too, but it is a matter of taste!

If your models are not associated or have more deep associations, Fissio's answer works great.

Answer 2

You can nest the queries like this:

index: function(req, res ){
    var userId = req.session.passport.user;

    // here i want to get data from user collection and want to send to the view
    User.findOne({id: userId}).exec(function(err, user) { // `id` might be `$id` in mongodb?
        Dashboard.find({posted_by: {$in: [userId]}}).exec(function(err, result) {
            if(err){
                sails.log("----Error----"+err);
            }else{
                res.view('dashboard/index',{'result': result, 'user': user}); 
            }
        });
    })
}

As the comment states, mongoDB might have $id instead of id so try it if this doesn't work.

Another method - which I prefer, to prevent callback hell issues - is to use Bluebird, which would work as follows:

var Promise = require('bluebird');

...


index: function(req, res ){
    var userId = req.session.passport.user;

    // here i want to get data from user collection and want to send to the view
    Promise.all([
        User.findOne({id: userId}),
        Dashboard.find({posted_by: {$in: [userId]}})
    ])
    .spread(function(user, result) {
        return res.view('dashboard/index',{'result': result, 'user': user}); 
    })
    .catch(function(err) {
        return res.serverError({status: 500, message: 'Something went wrong :('})
    })
}