Where condition for joined table in Sequelize ORM

JavascriptSqlPostgresqlOrmsequelize.js

Javascript Problem Overview


I want to get query like this with sequelize ORM:

SELECT "A".*,      
FROM "A" 
LEFT OUTER JOIN "B" ON "A"."bId" = "B"."id"
LEFT OUTER JOIN "C" ON "A"."cId" = "C"."id"
WHERE ("B"."userId" = '100'
       OR "C"."userId" = '100')

The problem is that sequelise not letting me to reference "B" or "C" table in where clause. Following code

A.findAll({
    include: [{
		model: B,
		where: {
			userId: 100
		},
		required: false

	}, {
		model: C,
		where: {
			userId: 100
		},
		required: false
	}]
] 

gives me

SELECT "A".*,      
FROM "A" 
LEFT OUTER JOIN "B" ON "A"."bId" = "B"."id" AND "B"."userId" = 100
LEFT OUTER JOIN "C" ON "A"."cId" = "C"."id" AND "C"."userId" = 100

which is completely different query, and result of

A.findAll({
    where: {
	    $or: [
		    {'"B"."userId"' : 100},
		    {'"C"."userId"' : 100}
	    ]
    },
    include: [{
		model: B,
		required: false

	}, {
		model: C,
		required: false
	}]
] 

is no even a valid query:

SELECT "A".*,      
FROM "A" 
LEFT OUTER JOIN "B" ON "A"."bId" = "B"."id"
LEFT OUTER JOIN "C" ON "A"."cId" = "C"."id"
WHERE ("A"."B.userId" = '100'
       OR "A"."C.userId" = '100')

Is first query even possible with sequelize, or I should just stick to raw queries?

Javascript Solutions


Solution 1 - Javascript

Wrap the columns which reference joined tables in $$

A.findAll({
    where: {
        $or: [
            {'$B.userId$' : 100},
            {'$C.userId$' : 100}
        ]
    },
    include: [{
        model: B,
        required: false

    }, {
        model: C,
        required: false
    }]
}); 

Solution 2 - Javascript

Add the where condition in the include, along with join.

    {
       model: C,
       where: {
        id: 1
       }
   }

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionfourslashwView Question on Stackoverflow
Solution 1 - JavascriptJan Aagaard MeierView Answer on Stackoverflow
Solution 2 - JavascriptJaskaran SinghView Answer on Stackoverflow