Sequelize.js joins to table twice using hasMany
I am using Sequelize.js for a double join on the same table. I have a set of Team objects and a set of Game objects. The hasMany Games team, so it will have foreign keys in the game table, but each game has two teams, so I need to join the table twice. What is the best way to do this with an ORM continuation.
Team = sequelize.define('teams',{
name : Sequelize.STRING,
location : Sequelize.STRING,
});
Game = sequelize.define('games',{
homeTeamId : Sequelize.INTEGER,
awayTeamId : Sequelize.INTEGER,
location : Sequelize.STRING,
});
// Associations
Game.hasOne(Team, {foreignKey : 'homeTeamId'});
.hasOne(Team, {foreignKey : 'awayTeamId'});
Team.hasMany(Game);
Thank!
source to share
This is a really interesting question. This is not supported in the sequel at this time, but it definitely does. I have outlined two ways:
var Sequelize = require('./index');
var sequelize = new Sequelize('sequelize_test', 'root', null, {
host: "127.0.0.1",
port: 3306
});
var Team = sequelize.define('teams',{
name : Sequelize.STRING,
location : Sequelize.STRING
}, {
instanceMethods: {
getGamesOne: function () {
var chainer = new Sequelize.Utils.QueryChainer();
chainer.add(this.getHomeGames());
chainer.add(this.getAwayGames());
return new Sequelize.Utils.CustomEventEmitter(function (emitter) {
chainer.run().done(function (err, results) {
var home = results[0],
away = results[1];
if (err) emitter.emit('error', err)
else emitter.emit('success', home.concat(away));
});
}).run();
},
getGamesTwo: function () {
return Game.findAll({ where: ["homeTeamId = ? OR awayTeamId = ?", this.id, this.id ]})
}
}
});
var Game = sequelize.define('games',{
homeTeamId : Sequelize.INTEGER,
awayTeamId : Sequelize.INTEGER,
location : Sequelize.STRING
});
// Associations
Game.belongsTo(Team, {foreignKey : 'homeTeamId'})
.belongsTo(Team, {foreignKey : 'awayTeamId'});
Team.hasMany(Game, { as: 'AwayGames', foreignKey : 'awayTeamId'});
Team.hasMany(Game, { as: 'HomeGames', foreignKey : 'homeTeamId'});
Team.find(1).done(function (err, team) {
team.getGamesOne().done(function(err, games) {
console.log(games);
});
team.getGamesTwo().done(function(err, games) {
console.log(games);
});
})
GetGamesOne uses sequelize assocations to fetch home and guest games separately and join them before returning them to you. GetGamesTwo manually creates the request, so you only talk to the DB once. Pick whichever you prefer - one thing might not be very clear what's going on, but you are using sequelize to build the query, so even if you change the key names later it will still work. Two are very short and straightforward, but you have to handle the request yourself.
I also changed your attitude from game to team to belongsTo
- this means the foreign key is in the games table.
Good luck using Sequelize; -)
source to share