Sequelize - Join with multiple column

Question

I like to convert the following query into sequelize code

select * from table_a 
inner join table_b 
on table_a.column_1 = table_b.column_1
and table_a.column_2 = table_b.column_2

I have tried many approaches and followed many provided solution but I am unable to achieve the desired query from sequelize code.

The max I achieve is following :

select * from table_a 
inner join table_b 
on table_a.column_1 = table_b.column_1

I want the second condition also.

and table_a.column_2 = table_b.column_2

any proper way to achieve it?

Answer 1

You need to define your own on clause of the JOIN statement

ModelA.findAll({
    include: [
        {
            model: ModelB,
            on: {
                col1: sequelize.where(sequelize.col("ModelA.col1"), "=", sequelize.col("ModelB.col1")),
                col2: sequelize.where(sequelize.col("ModelA.col2"), "=", sequelize.col("ModelB.col2"))
            },
            attributes: [] // empty array means that no column from ModelB will be returned
        }
    ]
}).then((modelAInstances) => {
    // result...
});

Answer 2

Regarding @TophatGordon 's doubt in accepted answer's comment: that if we need to have any associations set up in model or not.
Also went through the github issue raised back in 2012 that is still in open state.
So I was also in the same situation and trying to setup my own ON condition for left outer join.
When I directly tried to use the on: {...} inside the Table1.findAll(...include Table2 with ON condition...), it didn't work. It threw an error:

EagerLoadingError [SequelizeEagerLoadingError]: Table2 is not associated to Table1!

My use case was to match two non-primary-key columns from Table1 to two columns in Table2 in left outer join. I will show how and what I acheived:

---

Don't get confused by table names and column names, as I had to change them from the original ones that I used.

SO I had to create an association in Table1(Task) like:

Task.associate = (models) => {    

Task.hasOne(models.SubTask, {
        foreignKey: 'someId', // <--- one of the column of table2 - SubTask: not a primary key here in my case; can be primary key also
        sourceKey: 'someId', // <---  one of the column of table1 - Task: not a primary key here in my case; can be a primary key also
        scope: {
            [Op.and]: sequelize.where(sequelize.col("Task.some_id_2"),
                // '=',
                Op.eq, // or you can use '=',
                sequelize.col("subTask.some_id_2")),
        },
        as: 'subTask',
        // no constraints should be applied if sequelize will be creating tables and unique keys are not defined, 
        //as it throws error of unique constraint            
        constraints: false, 
    });
};

So the find query looks like this :

Task.findAll({
    where: whereCondition,
    // attributes: ['id','name','someId','someId2'],
    include: [{
        model: SubTask, as: 'subTask', // <-- model name and alias name as defined in association 
        attributes: [], // if no attributes needed from SubTask - empty array
    },
    ],
});

Resultant query:

• One matching condition is taken from [foreignKey] = [sourceKey]
• Second matching condition is obtained by sequelize.where(...) used in scope:{...}

select
  "Task"."id",
  "Task"."name",
  "Task"."some_id" as "someId",
  "Task"."some_id_2" as "someId2"
from
  "task" as "Task"
left outer join "sub_task" as "subTask" on
  "Task"."some_id" = "subTask"."some_id"
  and "Task"."some_id_2" = "subTask"."some_id_2";

---

Another approach to achieve same as above to solve issues when using Table1 in include i.e. when Table1 appears as 2nd level table or is included from other table - say Table0

Task.associate = (models) => {    

Task.hasOne(models.SubTask, {
        foreignKey: 'someId', // <--- one of the column of table2 - SubTask: not a primary key here in my case; can be primary key also
        sourceKey: 'someId', // <---  one of the column of table1 - Task: not a primary key here in my case; can be a primary key also
        as: 'subTask',
        // <-- removed scope -->
        // no constraints should be applied if sequelize will be creating tables and unique keys are not defined, 
        //as it throws error of unique constraint            
        constraints: false, 
    });
};

So the find query from Table0 looks like this : Also the foreignKey and sourceKey will not be considered as we will now use custom on: {...}

Table0.findAll({
    where: whereCondition,
    // attributes: ['id','name','someId','someId2'],
    include: {
        model: Task, as: 'Table1AliasName', // if association has been defined as alias name 
        include: [{
            model: SubTask, as: 'subTask', // <-- model name and alias name as defined in association 
            attributes: [], // if no attributes needed from SubTask - empty array
            on: {
                [Op.and]: [
                    sequelize.where(
                        sequelize.col('Table1AliasName_OR_ModelName.some_id'),
                        Op.eq, // '=',
                        sequelize.col('Table1AliasName_OR_ModelName->subTask.some_id')
                    ),
                    sequelize.where(
                        sequelize.col('Table1AliasName_OR_ModelName.some_id_2'),
                        Op.eq, // '=',
                        sequelize.col('Table1AliasName_OR_ModelName->subTask.some_id_2')
                    ),
                ],
            },
        }],
    }
});

Skip below part if your tables are already created...

---

Set constraints to false, as if sequelize tries to create the 2nd table(SubTask) it might throw error (DatabaseError [SequelizeDatabaseError]: there is no unique constraint matching given keys for referenced table "task") due to following query:

create table if not exists "sub_task" ("some_id" INTEGER, "some_id_2" INTEGER references "task" ("some_id") on delete cascade on update cascade, "data" INTEGER);

If we set constraint: false, it creates this below query instead which will not throw unique constraint error as we are referencing non-primary column:

create table if not exists "sub_task" ("some_id" INTEGER, "some_id_2" INTEGER, "data" INTEGER);