SELECT "Apple"."id", "Final"."id"
FROM "Apple"
LEFT OUTER JOIN "Camel" ON "Apple"."id" = "Camel"."apple_id"
LEFT OUTER JOIN "Dad" ON "Camel"."id" = "Dad"."camel_id"
INNER JOIN "Final" ON "Camel"."final_id" = "Final"."id" OR "Dad"."final_id" = "Final"."id";

This is what I'm trying to achieve. I have a column Apple which is referenced by 2 columns Camel and Dad which reference column Final but only one of Camel or Dad with hold a reference in a row. So I'm joining in the last sql line against both columns. Is this possible in sequelize

Apple.findAll({
  include: [
    {model: Camel, include: [Final]},
    {model: Dad,   include: [Final]},
  ],
});

This is a sample of what the query currently looks like. I have tried adding required: true to both inner includes and with the Final includes. I tried adding right: true Both did not work

CodePudding user response：

If raw sql is not an option, you can try with include[].on option - should work as where option.

1. Raw sql:

const [results, metadata] = await sequelize.query("UPDATE users SET y = 42 WHERE x = 12");

2. include[].on option:

const { Op } = require("sequelize");

Apple.findAll({
  include: [
    {model: Camel},
    {model: Dad},
    {
      model: Final,
      required: true,
      on: { 
        '$Final.id$': {
          [Op.or]: [ Sequelize.col('Camel.final_id'), Sequelize.col('Dad.final_id') ]
        }
      }
    }
  ],
});