Targeting a column after an 'include'/eager-loading

Question

I'm trying to write a sequelize function that looks like this SQL code:

SELECT * from events INNER JOIN corrections on events.correctionId
= correction.id WHERE events.failureId =  1234 OR 
corrections.failureId = 1234

Basically, I have a table (events) that has a foreign key of failureId and correctionId. The correction table also has a foreign key to failure table. I need all the rows in events where the failureId is equal to a particular AND all the rows where the failureId on the corrections table is equal to that number.

The part I'm having trouble with is referencing that corrections.failureId column that results from that inner join. I've tried a bunch of variations but nothing is working. Here's what I have so far:

Event.findAll({
      include: [correction],
      where: { [sequelize.Op.or]: [{ failID }, {????}] }

Eugene Shilin · Accepted Answer · 2019-03-25T20:43:55.527

1) You can use top level where to move your query to main where query from ON where (http://docs.sequelizejs.com/manual/models-usage.html#top-level-where-with-eagerly-loaded-models)

Event.findAll({
      include: [correction],
      where: { [sequelize.Op.or]: [{ failureId }, { '$correction.failureId$': failureId }] }] }
})

2) By default sequelize will use LEFT OUTER JOIN. To change this behaviour you need to either pass required to include like:

Event.findAll({
      include: [{ model: correction, required: true }],
      where: { [sequelize.Op.or]: [{ failureId }, { '$correction.failureId$': failureId }] }] }
})

or just add where statement to the included model like:

Event.findAll({
      include: [{ model: correction, where: {...} }],
      where: { [sequelize.Op.or]: [{ failureId }, { '$correction.failureId$': failureId }] }] }
})

So in your example you need:

Event.findAll({
      include: [{ model: correction, required: true }],
      where: { [sequelize.Op.or]: [{ failureId }, { '$correction.failureId$': failureId }] }] }
})

Thanks! This worked. I actually saw that in the docs before posting but I wasn't sure what it meant by 'top level where' and 'main level'. Do you mind explaining what it means by that? Thanks! — John Doe, Mar 25 '19 at 21:28
You can normally filter data you're joining right after `ON` keyword or you can use `WHERE` to filter already joined rows. See this answer: https://stackoverflow.com/a/354094/5624993 — Eugene Shilin, Mar 26 '19 at 12:04

Targeting a column after an 'include'/eager-loading

1 Answers1