Sequelize include is not working correctly on many-to-many with trough table with a type column

Question

In my many-to-many table I have a type column and Foo model that references Bar under several aliases.

The include works only correctly with the required property set to true. When fetching a Foo and some associated Bars, I only receive a subset.

I added an example below that can be executed to demonstrate the issue.

const { Sequelize, DataTypes } = require('sequelize')
const sequelize = new Sequelize('sqlite::memory:', {
  define: { timestamps: false }
})

// defining the models to relate to each other
const Foo = sequelize.define('foo', { name: DataTypes.STRING })
const Bar = sequelize.define('bar', { status: DataTypes.STRING })

// defining the model for the relation
const FooBar = sequelize.define('fooBar', {
  fooId: { type: DataTypes.STRING(32), primaryKey: true },
  barId: { type: DataTypes.STRING(32), primaryKey: true },
  type: { type: DataTypes.ENUM('x', 'y', 'z'), primaryKey: true }
}, { tableName: 'foo_bar' })

// defining the relationships
Foo.belongsToMany(Bar, { through: { model: FooBar, unique: false, scope: { type: 'x' } }, as: 'xs' })
Foo.belongsToMany(Bar, { through: { model: FooBar, unique: false, scope: { type: 'y' } }, as: 'ys' })
Foo.belongsToMany(Bar, { through: { model: FooBar, unique: false, scope: { type: 'z' } }, as: 'zs' });

(async () => {
  await sequelize.sync()

  // Instantiating the models
  await Foo.bulkCreate([
    { name: 'test' },
    { name: 'empty' },
    { name: 'greenhead' }
  ])

  await Bar.bulkCreate([
    { status: 'The Big Clash' },
    { status: 'Winter Showdown' },
    { status: 'Summer Beatdown' },
    { status: 'Test Beatdown' },
    { status: 'wrong' }
  ])

  // Instantiating the relationships
  await FooBar.bulkCreate([
    { fooId: 1, barId: 1, type: 'x' },
    { fooId: 1, barId: 1, type: 'y' },
    { fooId: 1, barId: 1, type: 'z' },
    { fooId: 1, barId: 2, type: 'y' },
    { fooId: 1, barId: 3, type: 'z' }
  ])

// This query outputs the correct associations but misses the Foo instances with not association to a Bar instance
const foos = await Foo.findAll({
    include: [{
      model: Bar,
      as: 'xs',
      required: true
    }, {
      model: Bar,
      as: 'ys',
      required: true
    }, {
      model: Bar,
      as: 'zs',
      required: true
    }]
  })
  console.log(foos.map(f => f.toJSON()))

// This query outputs all Foo instances but the associations are messed up.
  const foos2 = await Foo.findAll({
    include: [{
      model: Bar,
      as: 'xs'
    }, {
      model: Bar,
      as: 'ys'
    }, {
      model: Bar,
      as: 'zs'
    }]
  })
  console.log(foos2.map(f => f.toJSON()))

})()

This is the output of the first findAll with required: true option

[
{ id: 1,
  name: 'test',
  xs: [ { id: 1, status: 'The Big Clash', fooBar: [Object] } ],
  ys:
   [ { id: 1, status: 'The Big Clash', fooBar: [Object] },
     { id: 2, status: 'Winter Showdown', fooBar: [Object] } ],
  zs:
   [ { id: 1, status: 'The Big Clash', fooBar: [Object] },
     { id: 3, status: 'Summer Beatdown', fooBar: [Object] } ] }
]

But without the required: true option the result is:

[ { id: 1, name: 'test', xs: [{ id: 1, status: 'The Big Clash', fooBar: [Object] }, { id: null, status: null, fooBar: [Object] } ], ys: [], zs: [] },
  { id: 2, name: 'empty', xs: [], ys: [], zs: [] },
  { id: 3, name: 'greenhead', xs: [], ys: [], zs: [] }
]

Does someone has an idea on the correct way to include the Bars in the findAll? I want to perform one query to get all Foo instances and the associations with Bar if there are any. I don't understand why the last query the associations with the Bar instances are completely messed up.

Thanks

Answer 1

I created few hasMany associations & again queried via findAll. Advantage: we now dont need to use required: true while including.

Additions + Changes:

// Additions
Foo.hasMany(FooBar, { as: 'fbxs', foreignKey: 'fooId', scope: { type: 'x' }, });
Foo.hasMany(FooBar, { as: 'fbys', foreignKey: 'fooId', scope: { type: 'y' }, });
Foo.hasMany(FooBar, { as: 'fbzs', foreignKey: 'fooId', scope: { type: 'z' }, });
FooBar.belongsTo(Bar, { as: 'fbb', foreignKey: 'barId' });

// Changes
const foo2 = await Foo.findAll({
  // where: {
  //   // name: ['test', 'empty'], // if you filter by name, only matching Foos will be received
  // },
  include: [{
    model: FooBar, as: 'fbxs',
    include: [{
      model: Bar, as: 'fbb'
    }],
  }, {
    model: FooBar, as: 'fbys',
    include: [{
      model: Bar, as: 'fbb'
    }],
  }, {
    model: FooBar, as: 'fbzs',
    include: [{
      model: Bar, as: 'fbb'
    }],
  }],
});
// see this original output so you have a idea
// console.log(JSON.stringify(foo2, null, 2));

// formatting as per needs
const formattedFoo = foo2.map(foo => {
  const {
    fbxs,
    fbys,
    fbzs,
    ...fooData
  } = foo.toJSON();
  // fbxs, fbys, fbzs, are in form: [ { ...FooBarData, fbb: {...BarData } } ]
  // converting to: [ { ...BarData} ] 
  return ({
    ...fooData,
    xs: fbxs.map(fb => fb.fbb),
    ys: fbys.map(fb => fb.fbb),
    zs: fbzs.map(fb => fb.fbb),
  });
})
console.log(JSON.stringify(formattedFoo, null, 2));

Output:
Note 1: We have not filtered Foo by name, hence all are given back.
Note 2: As soon as you add where:{ name: ['test', 'empty'] } - only those Foo will be returned that matches the name.

[
  {
    "id": 1,
    "name": "test",
    "xs": [ { "id": 1, "status": "The Big Clash" } ],
    "ys": [ { "id": 1, "status": "The Big Clash" }, { "id": 2, "status": "Winter Showdown" } ],
    "zs": [ { "id": 1, "status": "The Big Clash" }, { "id": 3, "status": "Summer Beatdown" } ]
  },
  {
    "id": 2,
    "name": "empty",
    "xs": [ { "id": 1, "status": "The Big Clash" } ],
    "ys": [ { "id": 2, "status": "Winter Showdown" }, { "id": 4, "status": "Test Beatdown" } ],
    "zs": [ { "id": 1, "status": "The Big Clash" }, { "id": 3, "status": "Summer Beatdown" } ]
  },
  {
    "id": 3,
    "name": "greenhead",
    "xs": [],
    "ys": [],
    "zs": []
  },
  {
    "id": 4,
    "name": "not_spock",
    "xs": [],
    "ys": [],
    "zs": []
  },
  {
    "id": 5,
    "name": "bowl_of_petunias",
    "xs": [],
    "ys": [],
    "zs": []
  }
]

Query generated by above findAll:

SELECT
    `foo`.`id`,
    ...all columns
FROM
    `foos` AS `foo`
LEFT OUTER JOIN `foo_bar` AS `fbxs` ON
    `foo`.`id` = `fbxs`.`fooId`
    AND `fbxs`.`TYPE` = 'x'                 <--------- type condition applied here after joining `foo_bar` before joining to `bars`
LEFT OUTER JOIN `bars` AS `fbxs->fbb` ON
    `fbxs`.`barId` = `fbxs->fbb`.`id`
LEFT OUTER JOIN `foo_bar` AS `fbys` ON
    `foo`.`id` = `fbys`.`fooId`
    AND `fbys`.`TYPE` = 'y'                 <--------- type condition applied here after joining `foo_bar` before joining to `bars`
LEFT OUTER JOIN `bars` AS `fbys->fbb` ON
    `fbys`.`barId` = `fbys->fbb`.`id`
LEFT OUTER JOIN `foo_bar` AS `fbzs` ON
    `foo`.`id` = `fbzs`.`fooId`
    AND `fbzs`.`TYPE` = 'z'                 <--------- type condition applied here after joining `foo_bar` before joining to `bars`
LEFT OUTER JOIN `bars` AS `fbzs->fbb` ON
    `fbzs`.`barId` = `fbzs->fbb`.`id`;

---

---

Old answer in case is helpful to anyone.

I tried to understand what you need.
I ran a findAll with some addition. Please check if the below result is what you need.

// added extra connections
await FooBar.bulkCreate([
    { fooId: 1, barId: 1, type: 'x' },
    { fooId: 1, barId: 1, type: 'y' },
    { fooId: 1, barId: 1, type: 'z' },
    { fooId: 1, barId: 2, type: 'y' },
    { fooId: 1, barId: 3, type: 'z' },
    // added extra associations
    { fooId: 2, barId: 1, type: 'x' },
    { fooId: 2, barId: 4, type: 'y' },
    { fooId: 2, barId: 1, type: 'z' },
    { fooId: 2, barId: 2, type: 'y' },
    { fooId: 2, barId: 3, type: 'z' },
  ]);

//....

const foo2 = await Foo.findAll({
    where: {
        name: ['test', 'empty'], // lets say you need 2 names
    },
    include: [{
        model: Bar, as: 'xs',
        required: true,
        through: { attributes: [] }, // this removes the association(so called join table)
    }, {
        model: Bar, as: 'ys',
        required: true,
        through: { attributes: [] },
    }, {
        model: Bar, as: 'zs',
        required: true,
        through: { attributes: [] },
    }],
});
console.log(JSON.stringify(foo2, null, 2));

Output foo2:

[
  {
    "id": 1,
    "name": "test",
    "xs": [ { "id": 1, "status": "The Big Clash" } ],
    "ys": [ { "id": 1, "status": "The Big Clash" }, { "id": 2, "status": "Winter Showdown" } ],
    "zs": [ { "id": 1, "status": "The Big Clash" }, { "id": 3, "status": "Summer Beatdown" } ]
  },
  {
    "id": 2,
    "name": "empty",
    "xs": [ { "id": 1, "status": "The Big Clash" } ],
    "ys": [ { "id": 2, "status": "Winter Showdown" }, { "id": 4, "status": "Test Beatdown" } ],
    "zs": [ { "id": 1, "status": "The Big Clash" }, { "id": 3, "status": "Summer Beatdown" } ]
  }
]

Query generated by above findAll

SELECT
    `foo`.`id`,
    ....all columns
FROM
    `foos` AS `foo`
LEFT OUTER JOIN `foo_bar` AS `xs->fooBar` ON
    `foo`.`id` = `xs->fooBar`.`fooId`
LEFT OUTER JOIN `bars` AS `xs` ON
    `xs`.`id` = `xs->fooBar`.`barId`
    AND `xs->fooBar`.`TYPE` = 'x'            <--------- type condition applied here after joining `bars`
LEFT OUTER JOIN `foo_bar` AS `ys->fooBar` ON
    `foo`.`id` = `ys->fooBar`.`fooId`
LEFT OUTER JOIN `bars` AS `ys` ON
    `ys`.`id` = `ys->fooBar`.`barId`
    AND `ys->fooBar`.`TYPE` = 'y'            <--------- type condition applied here after joining `bars`
LEFT OUTER JOIN `foo_bar` AS `zs->fooBar` ON
    `foo`.`id` = `zs->fooBar`.`fooId`
LEFT OUTER JOIN `bars` AS `zs` ON
    `zs`.`id` = `zs->fooBar`.`barId`
    AND `zs->fooBar`.`TYPE` = 'z';            <--------- type condition applied here after joining `bars`