Sequelize v5 Poorly Formatted Join with Nested Include

Asked Jan 14 '22 at 19:16

Active Jan 14 '22 at 19:16

Viewed 24 times

I am using Sequelize v6 with the mariadb dialect.

I have query that looks like this:

db.RunVersion.findAll({
  include: [{
      model: db.Run,
      include: [{
         as: "Watchers",
         model: db.RunWatchers,
         required: true,
         where: {id: user.id}
      }],
   }],
   where: where
}]

This produces a WHERE clause that looks like this:

FROM RunVersions AS RunVersion
LEFT OUTER JOIN (Runs AS Run
INNER JOIN RunWatchers AS Runs->Watchers ON Run.id = Run->Watchers.wid
AND Run->Watchers.un = 'mrprice') ON RrunVersion.rid = Run.id

This returns bad results. I expect the WHERE clause to look like this:

FROM RunVersions AS RunVersion
LEFT OUTER JOIN Runs AS Run ON RunVersion.rid = Run.id
INNER JOIN RunWatchers AS Run->Watchers ON Run.id = Run->Watchers.wid AND Run-Watchers.unname = 'mrprice'

Any ideas why this is happening?

asked Jan 14 '22 at 19:16

CycleGeek

did you try `subquery: false`? – Anatoly Jan 14 '22 at 20:36
Yes. Tried it on every part of the query and no luck. – CycleGeek Jan 14 '22 at 20:49
Should be at the root level of options. Did the SQL query change after that even a bit? – Anatoly Jan 14 '22 at 20:51
db.RunVersion.findAll({ include: [{ model: db.Run, include: [{ as: "Watchers", model: db.RunWatchers, required: true, where: {id: user.id} }], }], where: where, subquery: false }] Query looks the same. – CycleGeek Jan 14 '22 at 20:53
Sorry, my bad. It should be `subQuery` – Anatoly Jan 14 '22 at 21:08
Still the same. – CycleGeek Jan 14 '22 at 21:20
Can you indicate `required: false` for RunWatchers? – Anatoly Jan 14 '22 at 21:34
Yes, and that formats it correctly but brings back way more records. – CycleGeek Jan 14 '22 at 21:37
1

Then just add a condition to `RunWatchers` like `{ id: { [Op.ne]: null } }` – Anatoly Jan 14 '22 at 21:48
Kind of a hack but that worked! Thanks. – CycleGeek Jan 14 '22 at 22:16
It's because usually if you use LEFT OUTER JOIN then you need to use it in all further joins as well, otherwise, you can end up with not a result you're expecting – Anatoly Jan 15 '22 at 11:43

Sequelize v5 Poorly Formatted Join with Nested Include

0 Answers0