How to implement inner join in sails waterline

Question

// model Users.js

that is the users model:

module.exports = {
  autoCreatedAt: false,
  autoUpdatedAt: false,
  tableName : 'users',
  attributes: {
    user_id: {
       type: "string",
       primaryKey: true
     },
      name : {
        type : 'varchar'
      }, 
      register_items : {
        collection : 'register',
        via : 'business_owner_id',
        dominant : true
      }

  }
};
// model Register.js
that is register model 
module.exports = {

  autoCreatedAt: false,
  autoUpdatedAt: false,
  tableName: 'register_items',
  attributes: {
    register_id: {
      type: "integer",
      primaryKey: true
    },
    business_owner_id: {
      model: 'users'
    }
  }
};
// Controller 
module.exports = {
    getUser : (req,res) => {
        Users.find().populate('register_items')
        .then(users => {
            res.json({status : 200, data : users});
        }).catch(err => {
            res.json({status : 500, message : 'Error ' + err});
        });
    }
};

Please, elaborate your question and check [how to ask](https://stackoverflow.com/help) — Billal Begueradj, Aug 30 '17 at 06:49
just tell me the syntax for inner join in sails waterline i can do that with native query but wanna do in waterline — Abdul Jabbar, Aug 30 '17 at 06:53

score 2 · Answer 1 · answered Aug 30 '17 at 07:19

2

Users.findOne(user_id).populate('register_items')

answered Aug 30 '17 at 07:19

Crusader

1,182
11
22

score 2 · Answer 2 · answered Aug 31 '17 at 23:22

Sails Waterline works on top of both SQL and NOSQL databases - that means that imitating the functionality of an Inner Join may not be possible in the way you are used to in SQL.

As @Crusader pointed out in their answer, the way you set up your models gives you a nice one-liner for fetching a user with register items already attached:

Users.findOne(user_id).populate('register_items').exec(function(err, user) {
    // handle errors and empty results...
    console.log(user.register_items); // will be full objects
});

But it looks like you are already using code like that in your question.

Since that code has to work on top of NOSQL as well, you are not guaranteed that an Inner Join is being done by the database. It may just be two queries (one after another) under the hood:

Get my user
Get all associated registers
Attach them

And it would be even worse for many-to-many relations!

The bottom line is, if you are using waterline, then - same as if you were using mongo or any NOSQL - logic involving multiple models may be done by your app (your code or the waterline library), not by your database, and you should plan your data storage accordingly.

How to implement inner join in sails waterline

2 Answers2