sql - Sequelize BelongsToMany with custom join table primary key -

i have many-to-many-relationship join table in middle. tables cookoff, participant, , cookoffparticipant. should mention not allowing sequelize create or modify tables, mapping existing relationships. need understanding relationship options tells sequelize call foreign key relates join table main table.

as understand it, sequelize assumes cookoffid , participantid composite primary key on cookoffparticipant. in situation, require primary key identity column i'm calling cookoffparticipantid , creating unique index on cookoffid, participantid pair in cookoffparticipant table.

when attempt cookoff , participant data querying through cookoffparticipant table, sequelize using wrong key accomplish join. there must simple not doing. below table structure , query 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] [participant.participantid],      [participant].[name] [participant.name],      [cookoff].[cookoffid] [cookoff.cookoffid],      [cookoff].[title] [cookoff.title],      [cookoff].[eventdate] [cookoff.eventdate]  [cookoffparticipant] [cookoffparticipant]  left outer join [participant] [participant]      on [cookoffparticipant].[cookoffparticipantid] = [participant].[participantid]  -- should cookoffparticipant.participantid left outer join [cookoff] [cookoff]      on [cookoffparticipant].[cookoffparticipantid] = [cookoff].[cookoffid] -- should cookoffparticipant.cookoffid [cookoffparticipant].[cookoffid] = 1  , [cookoffparticipant].[participantid] = 6  order [cookoffparticipantid]  offset 0 rows fetch next 1 rows only; 

you can see sequelize trying join cookoffparticipant.cookoffparticipantid on participant.participantid, should cookoffparticipant.participantid = participant.participantid , cookoffid. doing wrong here?

thank in advance help.

here discussion of looking for. summed nicely saying should define both through table , in through table declaring belongsto references. issue used hasone instead of belongsto. think as keys backwards.

cookoff.hasmany(book, { through: cookoffparticipant }) participant.hasmany(user, { through: cookoffparticipant }) cookoffparticipant.belongsto(cookoff) cookoffparticipant.belongsto(participant) 

here code used test 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()); });