sequelize association On or ON

Question

Dears I have model order has association with OrderHasWarranty model

So OrderHasWarranty has 2 foreign key, I need Sequelize to check the include for both these foreign key (without using where condition)

so I used below associations in order model

Order.hasOne(models.OrderHasWarranty,{foreignKey:"orderId"});
Order.hasOne(models.OrderHasWarranty,{foreignKey:"warrantyFromOrderId"});

the raw query giving me :

LEFT OUTER JOIN `order_has_warranties` AS `OrderHasWarranty` 
ON `Order`.`id` = `OrderHasWarranty`.`warrantyFromOrderId`
WHERE `Order`.`id` = '25459';

basically what i need is to convert the below query using association

LEFT OUTER JOIN `order_has_warranties` 
AS `OrderHasWarranty` ON `Order`.`id` = `OrderHasWarranty`.`warrantyFromOrderId` 
OR `Order`.`id` = `OrderHasWarranty`.`orderId`   WHERE `Order`.`id` = '25459';

SO I need to use OR condition in associations, I tried using hasMany but no luck

thank you

oniramarf oniramarf · Accepted Answer · 2018-02-23T00:10:04

I don't think you could get exactly what you are looking for, but I'm pretty sure that making the following changes could produce the desired result for you:

Add an alias to relations (you could choose some more appropriate)

Order.hasOne(models.OrderHasWarranty,{as: "warranty1", foreignKey:"orderId"});
Order.hasOne(models.OrderHasWarranty,{as: "warranty2", foreignKey:"warrantyFromOrderId"});

Then, you can include both of them in your search, like this:

Order.find({
  where: {
    id: 25459
  },
  include: ['warranty1', 'warranty2']
}).then(...).catch(...);

This would produce something like this:

LEFT OUTER JOIN `order_has_warranties` AS `warranty1` 
  ON `Order`.`id` = `OrderHasWarranty`.`orderId`
LEFT OUTER JOIN `order_has_warranties` AS `warranty2` 
  ON `Order`.`id` = `OrderHasWarranty`.`warrantyFromOrderId`
WHERE `Order`.`id` = '25459'

EDIT: The error you are reporting might be caused by using the hasOne method to declare the association. The Sequelize Doc says:

Even though it is called a HasOne association, for most 1:1 relations you usually want the BelongsTo association since BelongsTo will add the foreignKey on the source where hasOne will add on the target.

So, by adding also the belongsTo association, like this:

OrderHasWarranty.belongsTo(models.Order, {as: "warranty1", foreignKey:"orderId"});
OrderHasWarranty.belongsTo(models.Order, {as: "warranty2", foreignKey:"warrantyFromOrderId"});

your error should be resolved.

sequelize association On or ON

1 Answers