Sails.js - Get an object (model) using multiple join

Question

I am new to node.js and newer to Sails.js framework. I am currently trying to work with my database, I don't understand all the things with Sails.js but I manage to do what I want step by step. (I am used to some PHP MVC frameworks so it is not too difficult to understand the structure.) Here I am trying to get a row from my database, using 2 `JOIN` clause. I managed to do this using `SQL` and the `Model.query()` function, but I would like to do this in a "cleaner" way. So I have 3 tables in my database: meta, lang and meta_lang. It's quite simple and a picture being better than words, here are some screenshots. meta lang meta_lang What I want to do is to get the row in `meta_table` that match with 'default' meta and 'en' lang (for example). Here are Meta and Lang models (I created them with `sails generate model` command and edited them with what I needed): Meta ``` module.exports = { attributes: { code : { type: 'string' }, metaLangs:{ collection: 'MetaLang', via: 'meta' } } }; ```…

Accepted Answer

Do you know what populate is for ? its for including the whole associated object when loading it from the database. Its practically the join you are trying to do, if all you need is row retrieval than quering the table without populate will make both functions you built work.

To me it looks like you are re-writing how Sails did the association. Id suggest giving the Associations docs another read in Sails documentation: Associations. As depending on your case you are just trying a one-to-many association with each table, you could avoid a middle table in my guess, but to decide better id need to understand your use-case.

When I saw the mySQL code it seemed to me you are still thinking in MySQL and PHP which takes time to convert from :) forcing the joins and middle tables yourself, redoing a lot of the stuff sails automated for you. I redone your example on 'disk' adapter and it worked perfectly. The whole point of WaterlineORM is to abstract the layer of going down to SQL unless absolutely necessary. Here is what I would do for your example, first without SQL just on a disk adapter id create the models :

```
  // Lang.js
  attributes: {
    id :{ type:  "Integer" , autoIncrement : true,  primaryKey: true },
    code :"string"
  }
```

you see what i did redundantly here ? I did not really need the Id part as Sails does it for me. Just an example.

```
  // Meta.js
  attributes: {
    code :"string"
  }
```

better :) ?

```
 // MetaLang.js
attributes: 
 {
      title : "string",
      desc : "string",
      meta_id : 
      {
       model : "meta",
      },
     lang_id : 
     {
       model : "lang",    
     }
  }
```

Now after simply creating the same values as your example i run sails console type :

```
 MetaLang.find({meta_id : 1 ,lang_id:2}).exec(function(er,res){
  console.log(res);
 });
```

Output >>>

```
sails> [ { meta_id: 1,
    lang_id: 2,
    title: 'My blog',
    id: 2 } ]
```

Now if you want to display what is meta with id 1 and what is lang with id 2, we use populate, but the referencing for join/search is just as simple as this.

```
sails> Meta_lang.find({meta_id : 1 ,lang_id:2}).populate('lang_id').populate('meta_id').exec(function(er,res){ console.log(res); });
undefined
sails> [ { 
  meta_id: 
     { code: 'default',
       id: 1 },
  lang_id: 
     { code: 'En',
       id: 2 },
    title: 'My blog',
    id: 2 } ]
```

At this point, id switch adapters to MySQL and then create the MySQL tables with the same column names as above. Create the FK_constraints and voila. Another strict policy you can add is to set up the 'via' and dominance on each model. you can read more about that in the Association documentation and it depends on the nature of association (many-to-many etc.)

To get the same result without knowing the Ids before-hand :

```
sails> Meta.findOne({code : "default"}).exec(function(err,needed_meta){
..... Lang.findOne({code : "En"}).exec(function(err_lang,needed_lang){
....... Meta_lang.find({meta_id : needed_meta.id , lang_id : needed_lang.id}).exec(function(err_all,result){
......... console.log(result);});
....... });
..... });
undefined
sails> [ { meta_id: 1,
    lang_id: 2,
    title: 'My blog',
    id: 2 } ]
```

Sails.js - Get an object (model) using multiple join

Solution

Problem