Sails.js Associations

Question

I start with sails.js and I am completely lost with my sql queries.

I have the following tables:

genres +-----------+--------------+------+-----+ | Field | Type | Null | Key | +-----------+--------------+------+-----+ | id | int(6) | NO | PRI | | name | varchar(100) | NO | | | slug | varchar(255) | NO | | | type | varchar(32) | NO | | | parent_id | int(11) | YES | MUL | +-----------+--------------+------+-----+ genres_radios +----------+--------+------+-----+ | Field | Type | Null | Key | +----------+--------+------+-----+ | genre_id | int(6) | NO | MUL | | radio_id | int(6) | NO | MUL | +----------+--------+------+-----+ radios +-----------+--------------+------+-----+ | Field | Type | Null | Key | +-----------+--------------+------+-----+ | id | int(5) | NO | PRI | | name | varchar(100) | NO | | | slug | varchar(100) | NO | | | url | varchar(100) | NO | | +-----------+--------------+------+-----+ 

I want to get a radio and related genres. I managed to do this using Model.query("Select * FROM...") but I would like to do this using the Model.query("Select * FROM...") method. I looked at the docs, but I got a little confused with "via" , "through" , ...

Answer 1

This should do it:

 // api/models/Genres.js module.exports = { attributes : { name : { type: 'string' }, slug : { type: 'string' }, type : { type: 'string' }, radios : { collection: 'Radios', through: 'genres_radios' } } } // api/models/Radios.js module.exports = { attributes : { name : { type: 'string' }, slug : { type: 'string' }, url : { type: 'string' }, genres : { collection: 'genre', through: 'genres_radios' } } } // api/models/Genres_radios.js module.exports = { attributes = { 'Genre_id': { columnName:'genre_id', type:'integer', foreignKey:'true', references:'genres', on:'id', via:'genres' }, 'Radio_id': { columnName:'radio_id', type:'integer', foreignKey:'true', references:'radios', on:'id', via:'radios' } } } 

And then you can make the following request:

 Radio.findOne({name:"RadioName"}).populate("genres").then(function(radio){ console.log(radio); }) 

Answer 2

Well, if you followed the documentation for Sails.js files and multi-factor documents , your models should look something like this:

 // api/models/genre.js module.exports = { attributes : { name : { type: 'string' }, slug : { type: 'string' }, type : { type: 'string' }, radios : { collection: 'radio', via: 'genres' } } } // api/models/radio.js module.exports = { attributes : { name : { type: 'string' }, slug : { type: 'string' }, url : { type: 'string' }, genres : { collection: 'genre', via: 'radios' } } } 

Numerous lookup tables will be created for you internally using the waterline. All you need to get genres for your radio is the genres attribute.

 Radio.findOne({name:"RadioName"}).populate("genres").then(function(radio){ console.log(radio); //radio.genres will have all the genres associated with this radio. }) 

I really recommend looking at multi-factor documents . They have exactly what you need.