AkAk47 AkAk47 - 11 days ago 6
Node.js Question

Join twice on the same ForeignKey

Im using Sequelize for my Querys.
Everything is going fine except for one Case.

I want this

return db.sequelize.query(`
SELECT k.name as Name,
k._id as _id,
k.notice as notice,
dv1.value as value1,
dv2.value as value2,
dv2._id as v_id
FROM dict_Keys k
LEFT JOIN dict_Values dv1 ON k._id=dv1.key_id and dv1.lang_id=` + req.params.id + `
LEFT JOIN dict_Values dv2 ON k._id=dv2.key_id and dv2.lang_id=` + req.params.id2 + ``, {
type: Sequelize.QueryTypes.SELECT
})
.then(handleEntityNotFound(res))
.then(respondWithResult(res))
.catch(handleError(res));


translate in the ORM Model.

But I couldnt find how to reference twice on the same ForeignKey.

Things as including Model twice is throwing Error, cant include the same Model just twice.

Declaring the second Model with an Alias needs then to create the same Foreignkey twice but named different, that is not wanted!

Is there a Solution in Sequelize for that or at the moment only to declare the ForeignKey twice ?

EDIT1:

The ForeignKey Connection

db.DictKey.hasMany(db.DictValue, {
foreignKey: 'key_id'
});


Try to Include it twice on same ForeignKey

export function showAll(req, res) {
return DictKey.findAll({
include:[{
model: DictValue,
where:{
lang_id: req.params.id
}
},
{
model: DictValue,
where:{
lang_id: req.params.id
}
}
]
})
.then(handleEntityNotFound(res))
.then(respondWithResult(res))
.catch(handleError(res));
}


Errors The Correlation name 'dict_Values' is specified multiple times in a From clause.

Assign it twice

db.DictValue.belongsTo(db.DictKey,{
as: 'lang1',
foreignKey: 'key_id'
});
db.DictValue.belongsTo(db.DictKey,{
as: 'lang2',
foreignKey: 'key_id'
});


Something like this I tried as well,but dont remember exact, but it generated 2 ForeignKeys which is not wished.

Answer

Solution is to set Aliases for the Keys.
My fault was to do it in the belongsTo Association.
It has to be in the hasMany Association.

db.DictKey.hasMany(db.DictValue, {
  foreignKey: 'key_id',
  as : 'value1'
});
db.DictValue.belongsTo(db.DictKey,{
  foreignKey : 'key_id',
});
db.DictKey.hasMany(db.DictValue, {
  foreignKey: 'key_id',
  as : 'value2'
});
db.DictValue.belongsTo(db.DictKey,{
  foreignKey : 'key_id',
});