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

Question

I am new to node.js and newer in the Sails.js framework.

I am currently trying to work with my database, I do not understand all things with Sails.js, but I am able to do what I want, step by step. (I'm used to some PHP MVC frameworks, so it's not hard to understand the structure.)

Here I am trying to get a row from my database using 2 JOIN . I managed to do this using the SQL function and Model.query() , but I would like to make it "cleaner."

So, I have 3 tables in my database: meta, lang and meta_lang. It's pretty simple, and the image is better than words, here are a few screenshots.

meta

languages

meta_lang

What I want to do is get a line in meta_table that matches the default metaphone and for example en and lang (for example).

Meta and Lang models are shown here (I created them using the sails generate model command and edited them with what I need):

Meta STRONG>

 module.exports = { attributes: { code : { type: 'string' }, metaLangs:{ collection: 'MetaLang', via: 'meta' } } }; 

Lang

 module.exports = { attributes: { code : { type: 'string' }, metaLangs:{ collection: 'MetaLang', via: 'lang' } } }; 

And here is my MetaLang model, with three functions that I created to test several methods. The first findCurrent function works fine, but as you can see, I had to write SQL. I want to avoid this, if possible, I consider it cleaner (and I would like to use the Sails.js tools as often as I can).

 module.exports = { tableName: 'meta_lang', attributes: { title : { type: 'string' }, description : { type: 'text' }, keywords : { type: 'string' }, meta:{ model:'Meta', columnName: 'meta_id' }, lang:{ model:'Lang', columnName: 'lang_id' } }, findCurrent: function (metaCode, langCode) { var query = 'SELECT ml.* FROM meta_lang ml INNER JOIN meta m ON m.id = ml.meta_id INNER JOIN lang l ON l.id = ml.lang_id WHERE m.code = ? AND l.code = ?'; MetaLang.query(query, [metaCode, langCode], function(err, metaLang) { console.log('findCurrent'); if (err) return console.log(err); console.log(metaLang); // OK this works exactly as I want (I would have prefered a 'findOne' result, only 1 object instead of an array with 1 object in it, but I can do with it.) }); }, findCurrentTest: function (metaCode, langCode) { Meta.findByCode(metaCode).populate('metaLangs').exec(function(err, metaLang) { console.log('findCurrentTest'); if (err) return console.log(err); console.log(metaLang); // I get what I expected (though not what I want): my meta + all metaLangs related to meta with code "default". // What I want is to get ONE metaLang related to meta with code "default" AND lang with code "en". }); }, findCurrentOthertest: function (metaCode, langCode) { MetaLang.find().populate('meta', {where: {code:metaCode}}).populate('lang', {where: {code:langCode}}).exec(function(err, metaLang) { console.log('findCurrentOthertest'); if (err) return console.log(err); console.log(metaLang); // Doesn't work as I wanted: it gets ALL the metaLang rows. }); } }; 

I also tried to get my meta by code first, then my Lang by code and MetaLang using Meta.id and Lang.id. But I would like to avoid 3 queries when I can only have one.

What I'm looking for will be something like MetaLang.find({meta.code:"default", lang.code:"en"}) .

I hope you have all the necessary information, just comment and ask for more if you do not.

Answer 1

Do you know what settlement is? it to include the entire linked object when loading from the database. In fact, this is the connection that you are trying to make, if all you need is a row search than querying a table without filling, both functions that you created will do.

It seems to me that you are rewriting how Sails made the connection. Id suggests giving Associates documents another read in the Sails Documentation: Associations . Since, depending on your case, you are simply trying to establish a one-to-many relationship with each table, you can avoid the middle table in my guess, but in order to better understand the identifier, you need to understand your use case.

When I saw the mySQL code, it seemed to me that you are still thinking in MySQL and PHP that it takes time to convert from :), forcing the joins and middle tables themselves to redo many sails that are automatic for you. I redid your example on the "disk" adapter and it worked perfectly. The whole point of WaterlineORM is to abstract the level of transition to SQL, if absolutely necessary.
Here is what I would do for your example: first, without SQL, only create models on the drive adapter ID:

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

Do you see what I did in abundance here? I don't need the Id part, as Sails does this for me. Just an example.

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

it's better:)?

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

Now, just by creating the same values ​​as in your example, I run the sail console type:

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

Exit →>

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

Now, if you want to display metafiles with identifier 1 and what is lang with id 2, we use populate, but the link to connect / search is 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 switches the adapters to MySQL, and then creates the MySQL tables with the same column names as above. Create FK_constraints and voila.
Another strict policy you can add is to set up an end-to-end and dominant role for each model. You can learn more about this in the documentation of the Association, and it depends on the nature of the association (many-to-many, etc.).

To get the same result without knowing the identifiers in front of you:

 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 } ] 

Answer 2

You tried:

 findCurrentTest: function (metaCode, langCode) { Meta.findByCode(metaCode).populate('metaLangs', {where: {code:langCode}}).exec(function(err, metaLang) { console.log('findCurrentTest'); if (err) return console.log(err); console.log(metaLang); }); },