johnmalkovitch johnmalkovitch - 27 days ago 18
MySQL Question

Sails.js associations

I am beginning 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 retrieve the radios and their associated genres. I managed to do it using the Model.query("Select * FROM ...") but I'd like to do it using the populate method. I had a look at the docs, but I am a bit confused with the "via", "through", ...

Answer

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); 
})
Comments