Ernesto Jimenez Ernesto Jimenez - 17 days ago 5
Node.js Question

Sequelize self reference can´t make a join

I'm trying to make a many to many table with matches of different teams and it works, but when I tried to make a join I got this error:

Unhandled rejection Error: team is not associated to match!


Here is my code:

var Sequelize = require('sequelize');
var sequelize = new Sequelize('cricket', 'root', '');


var Team = sequelize.define('team', {
name: Sequelize.STRING,
});

var Match = sequelize.define('match', {
id: {
type: Sequelize.INTEGER,
primaryKey: true,
autoIncrement: true
},
scoreHome: Sequelize.INTEGER,
scoreAway: Sequelize.INTEGER,
});


Team.belongsToMany(Team, {as: 'Home', through: Match, foreignKey: 'homeTeamId'});
Team.belongsToMany(Team, {as: 'Away', through: Match, foreignKey: 'awayTeamId'});

sequelize.sync().then(function () {
Match.findAll({
include: [ Team ]
}).then(function (matches) {
console.log(matches)
});
});

Answer

You also need to provide the reverse relationship for Match.

In your current setup Team is associated with Team, not with Match. Match model is your through model.

For example valid association for Match would be :

var HomeTeam = Match.belongsTo( Team, { as: 'HomeTeam', foreignKey: 'homeTeamId' });
var AwayTeam = Match.belongsTo( Team, { as: 'AwayTeam', foreignKey: 'awayTeamId' })

sequelize.sync().then(function () {
    Match.findAll({
        include: [ HomeTeam, AwayTeam ]
    }).then(function (matches) {
        console.log(matches)
    });
});

// Makes the following query 

// SELECT `match`.`id`, `match`.`scoreHome`, `match`.`scoreAway`, `match`.`createdAt`, `match`.`updatedAt`, `match`.`homeTeamId`, `match`.`awayTeamId`, `HomeTeam`.`id` 
// AS `HomeTeam.id`, `HomeTeam`.`name` AS `HomeTeam.name`, `HomeTeam`.`createdAt` 
// AS `HomeTeam.createdAt`, `HomeTeam`.`updatedAt` AS `HomeTeam.updatedAt`, `AwayTeam`.`id` AS `AwayTeam.id`, `AwayTeam`.`name` AS `AwayTeam.name`, `AwayTeam`.`createdAt` AS `AwayTeam.createdAt`, `AwayTeam`.`updatedAt` AS `AwayTeam.updatedAt` FROM `matches` AS `match` LEFT OUTER JOIN `teams` AS `HomeTeam` ON `match`.`homeTeamId` = `HomeTeam`.`id` LEFT OUTER JOIN `teams` AS `AwayTeam` ON `match`.`awayTeamId` = `AwayTeam`.`id`