时间戳

首页 > 解决方案 > Sequelize include 在带有类型列的槽表的多对多上无法正常工作

sequelize.js - Sequelize include 在带有类型列的槽表的多对多上无法正常工作

问题描述

在我的多对多表中，我有一个类型列和 Foo 模型，它以多个别名引用 Bar。

include只有在属性required设置为 时才能正常工作true。在获取 aFoo和一些关联Bar的 s 时，我只收到一个子集。

我在下面添加了一个示例，可以执行该示例来演示该问题。

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()))

})()

这是第一个findAll选项required: true的输出

[
{ 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] } ] }
]

但如果没有该required: true选项，结果是：

[ { 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: [] }
]

有人对将 包含在Bars中的正确方法有想法findAll吗？我想执行一个查询来获取所有 Foo 实例以及与 Bar 的关联（如果有的话）。我不明白为什么最后一个查询与 Bar 实例的关联完全搞砸了。

谢谢

标签： sequelize.jsmany-to-many

解决方案

我创建了几个 hasMany 关联并再次通过 findAll 进行查询。优点：我们现在不需要required: true在包含时使用。

添加+更改：

// 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));

输出：
注 1：我们没有按名称过滤 Foo，因此全部返回。
注意 2：一旦添加where:{ name: ['test', 'empty'] }- 只会返回与名称匹配的 Foo 。

[
  {
    "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": []
  }
]

以上 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`;

旧答案以防万一对任何人都有帮助。

我试图了解你需要什么。
我运行了一个findAll并添加了一些内容。请检查以下结果是否是您需要的。

// 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));

输出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" } ]
  }
]
上面 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`

推荐阅读