Sequelize BelongsToMany with custom join table primary key

Question

I have a many-to-many-relationship with a join table in the middle. The tables are Cookoff, Participant, and CookoffParticipant. I should mention I am not allowing Sequelize to create or modify my tables, I am simply mapping my existing relationships. I need help understanding which relationship options tells sequelize what to call the foreign key that relates a join table to the main table.

As I understand it, Sequelize assumes that the CookoffID and ParticipantID are a composite primary key on CookoffParticipant. In my situation, I require the primary key to be an identity column I'm calling CookoffParticipantID and creating a unique index on the CookoffID, ParticipantID pair in the CookoffParticipant table.

When I attempt to get the cookoff and participant data by querying through the cookoffParticipant table, Sequelize is using the wrong key to accomplish the join. There must be something simple that I am not doing. Below is my table structure and the query with results.

Cookoff Table

var Cookoff = sequelize.define("Cookoff", {

    // Table columns

    CookoffID: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    Title: {
        type: DataTypes.STRING,
        allowNull: false
    },
    EventDate: {
        type: DataTypes.DATE,
        allowNull: false
    }
}, _.extend({},

    // Table settings
    defaultTableSettings,

    {
        classMethods: {
            associate: function(models) {
                Cookoff.belongsToMany(models.Participant, {
                    through: {
                        model: models.CookoffParticipant
                    },
                    as: "Cookoffs",
                    foreignKey: "CookoffID",
                    otherKey: "ParticipantID"
                });
            }
        }
    }
));

Participant table

var Participant = sequelize.define("Participant", {

    // Table columns
    ParticipantID: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    Name: {
        type: DataTypes.STRING(100),
        allowNull: false
    }

}, _.extend({},

    defaultTableSettings,

    {
        classMethods: {
            associate: function(models) {
                Participant.belongsToMany(models.Cookoff, {
                    through: {
                        model: models.CookoffParticipant
                    },
                    as: "Participants",
                    foreignKey: "ParticipantID",
                    otherKey: "CookoffID"
                });
            }
        }
    }
));

CookoffParticipant Table

var CookoffParticipant = sequelize.define("CookoffParticipant", {
    CookoffParticipantID: {
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: true
    },
    CookoffID: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: cookoff,
            key: "CookoffID"
        }
    },
    ParticipantID: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: participant,
            key: "ParticipantID"
        }
    }
}, _.extend(
    { },
    defaultTableSettings,
    {
        classMethods: {
          associate: function (models) {
              CookoffParticipant.hasOne(models.Cookoff, { foreignKey: "CookoffID" });
              CookoffParticipant.hasOne(models.Participant, { foreignKey: "ParticipantID" });

            }
        }
    }
));

My Query

return cookoffParticpants.findOne({
        where: { CookoffID: cookoffID, ParticipantID: participantID },
        include: [
            { model: participants },
            { model: cookoffs }
        ]
    });

The generated SQL

SELECT 
    [CookoffParticipant].[CookoffParticipantID], 
    [CookoffParticipant].[CookoffID], 
    [CookoffParticipant].[ParticipantID], 
    [Participant].[ParticipantID] AS [Participant.ParticipantID], 
    [Participant].[Name] AS [Participant.Name], 
    [Cookoff].[CookoffID] AS [Cookoff.CookoffID], 
    [Cookoff].[Title] AS [Cookoff.Title], 
    [Cookoff].[EventDate] AS [Cookoff.EventDate] 
FROM [CookoffParticipant] AS [CookoffParticipant] 
LEFT OUTER JOIN [Participant] AS [Participant] 
    ON [CookoffParticipant].[CookoffParticipantID] = [Participant].[ParticipantID]  -- This should be CookoffParticipant.ParticipantID
LEFT OUTER JOIN [Cookoff] AS [Cookoff] 
    ON [CookoffParticipant].[CookoffParticipantID] = [Cookoff].[CookoffID] -- This should be CookoffParticipant.CookoffID
WHERE [CookoffParticipant].[CookoffID] = 1 
AND [CookoffParticipant].[ParticipantID] = 6 
ORDER BY [CookoffParticipantID] 
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

You can see that Sequelize is trying to join CookoffParticipant.CookoffParticipantID ON Participant.ParticipantID, where it should be CookoffParticipant.ParticipantID = Participant.ParticipantID and similarly for CookoffID. What am I doing wrong here?

Thank you in advance for your help.

Answer 1

Here is a very good discussion of what you are looking for. They summed it up very nicely by saying that you should define both a through table and in the through table declaring the belongsTo references. Your issue is probably that you used hasOne instead of belongsTo. Also I think your as keys are backwards.

Cookoff.hasMany(Book, { through: CookoffParticipant })
Participant.hasMany(User, { through: CookoffParticipant })
CookoffParticipant.belongsTo(Cookoff)
CookoffParticipant.belongsTo(Participant)

Here is the code I used to test this out.

Cookoff.js

module.exports = (sequelize, DataTypes) => {
    var Cookoff = sequelize.define("Cookoff", {
        CookoffID: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        }
    }, _.extend(
        {},
        {
            classMethods: {
                associate: function(models) {
                    Cookoff.belongsToMany(models.Participant, {
                        through: models.CookoffParticipant,
                        foreignKey: "CookoffID",
                        otherKey: "ParticipantID"
                    });
                }
            }
        }
    ));
    return Cookoff;
};

Participant.js

module.exports = (sequelize, DataTypes) => {
    var Participant = sequelize.define("Participant", {
        ParticipantID: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        }
    }, _.extend(
        {},
        {
            classMethods: {
                associate: function(models) {
                    Participant.belongsToMany(models.Cookoff, {
                        through: models.CookoffParticipant,
                        foreignKey: "ParticipantID",
                        otherKey: "CookoffID"
                    });
                }
            }
        }
    ));
    return Participant;
};

CookoffParticipant.js

module.exports = (sequelize, DataTypes) => {
    var CookoffParticipant = sequelize.define("CookoffParticipant", {
        CookoffParticipantID: {
            type: DataTypes.INTEGER,
            allowNull: false,
            primaryKey: true,
            autoIncrement: true
        }
    }, _.extend(
        {},
        {
            classMethods: {
                associate: function(models) {
                    CookoffParticipant.belongsTo(models.Cookoff, { foreignKey: "CookoffID" });
                    CookoffParticipant.belongsTo(models.Participant, { foreignKey: "ParticipantID" });
                }
            }
        }
    ));
    return CookoffParticipant;
};

test.js

const db = require('../db');
const Cookoff = db.Cookoff;
const Participant = db.Participant;
const CookoffParticipant = db.CookoffParticipant;
let cookoff,
    participant;

Promise.all([
    Cookoff.create({}),
    Participant.create({})
]).then(([ _cookoff, _participant ]) => {
    cookoff = _cookoff;
    participant = _participant;

    return cookoff.addParticipant(participant);
}).then(() => {
    return CookoffParticipant.findOne({
        where: { CookoffID: cookoff.CookoffID, ParticipantID: participant.ParticipantID },
        include: [ Cookoff, Participant ]
    });
}).then(cookoffParticipant => {
    console.log(cookoffParticipant.toJSON());
});