eHattori eHattori - 4 days ago 6
Javascript Question

Make query using Sequelize and NodeJS with other key in primary key

I have a question that I lost time and nothing.

I have one model Price:

var price = sequelize.define('price', {
price_id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: DataType.INTEGER
},
carrier_mongo_id : {
type: DataType.STRING,
require : true,
references: {
model: "carrier",
key: "carrier_mongo_id"
},
onUpdate: 'cascade',
onDelete: 'cascade'
},
value: {
type: DataType.DECIMAL
}
});


I have other model Carrier:

var carrier = sequelize.define('carrier', {
carrier_id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Datatype.INTEGER
},
carrier_mongo_id: {
type: Datatype.STRING,
require : true
},
name: {
type: Datatype.STRING,
require : true
}
});


and make query like this:

carrierModel.hasMany(priceModel, {foreignKey: 'carrier_mongo_id'});

carrierModel.findAll({ include: [{ model: priceModel , required: true }] }).then(function(result){

var aCarriers = new Array();

result.forEach(function(carreteiro){
aCarriers.push(carreteiro.dataValues);
});

console.log(aCarriers);

}).catch(function(err){
console.log(err);
});


my query is :

SELECT
`carrier`.`carrier_id`,
`carrier`.`carrier_mongo_id`,
`carrier`.`name`,
`prices`.`price_id` AS `prices.price_id`,
`prices`.`carrier_mongo_id` AS `prices.carrier_mongo_id`,
`prices`.`value` AS `prices.value`
FROM
`carrier` AS `carrier`
INNER JOIN `price` AS `prices` ON `carrier`.`carrier_id` = `prices`.`carrier_mongo_id`


But I need this SQL query:

SELECT
`carrier`.`carrier_id`,
`carrier`.`carrier_mongo_id`,
`carrier`.`name`,
`prices`.`price_id` AS `prices.price_id`,
`prices`.`carrier_mongo_id` AS `prices.carrier_mongo_id`,
`prices`.`value` AS `prices.value`
FROM
`carrier` AS `carrier`
INNER JOIN `price` AS `prices` ON `carrier`.`carrier_mongo_id` = `prices`.`carrier_mongo_id`


I know that I can use this method Sequelize.query (...), but my question is:

How to make this query using Sequelize model

tks

Answer

I found the solution for my case guys =)

the fix is update the CarrierModel set up the carrier_mongo_id for primaryKey

 ........................
 carrier_mongo_id : {
    type: DataType.STRING,
    require : true,
    primaryKey: true,
    references: {
        model: "carrier",
        key: "carrier_mongo_id"
    },
    onUpdate: 'cascade',
    onDelete: 'cascade'
},
.....................

And the query is like :

 carreteiroModel.hasMany(precoModel, {foreignKey:         'carreteiro_mongo_id', otherKey: 'carreteiro_mongo_id'});
         carreteiroModel.findAll({ include: [{ model: precoModel ,    required: true }] }).
   then(function(result){ ...});

tks for you comments :)

Comments