mysql - SequelizeJS Eager 加载两个相同的 N:M 模型一个返回数据另一个不
问题描述
我有一个模型Link
和两个其他模型Overlay
& Tracker
。Link
两者都与模型有多对多的关系。数据在 MySQL 数据库中。
Overlay
&Tracker
使用相同的关联,Link
但当我尝试使用 sequlize 查询急切加载它们时,Tracker
值总是null
(返回为空数组)。
例如:
考虑这个示例查询。
const result = await Link.findAndCountAll({
where: {
userId: req.user.id
},
limit: limit,
offset: offset,
order: [
['createdAt', 'DESC']
],
include: [
{model: Tracker, as: 'trackers'},
{model: Overlay, as: 'overlays'}
]
});
返回的结果包含overlays
正确但trackers
始终为空数组。我真的很困惑,因为这两个模型在关联上是相同的。
楷模
链接.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const Link = sequelize.define('Link', {
name: DataTypes.STRING,
// ... removed for simplicity
}
}, {
freezeTableName: true,
tableName: 'links'
});
Link.associate = function(models) {
// associations can be defined here
Link.belongsTo(models.User, {
as: 'user',
foreignKey: 'userId'
});
Link.belongsToMany(models.Overlay, {
as: 'overlays',
through: models.LinkOverlays,
foreignKey: 'linkId',
targetKey: 'id',
});
Link.belongsToMany(models.Tracker, {
as: 'trackers',
through: models.LinkTrackers,
foreignKey: 'trackerId',
targetKey: 'id',
});
};
return Link;
};
Overlay.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const Overlay = sequelize.define('Overlay', {
name: DataTypes.STRING(100),
// ... removed for simplicity
}, {
freezeTableName: true,
tableName: 'overlays'
});
Overlay.associate = function(models) {
// associations can be defined here
Overlay.belongsTo(models.User, {
foreignKey: 'userId'
});
Overlay.belongsToMany(models.Link, {
as: 'links',
through: models.LinkOverlays,
foreignKey: 'overlayId',
targetKey: 'id'
});
};
return Overlay;
};
跟踪器.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const Tracker = sequelize.define('Tracker', {
name: DataTypes.STRING(100),
// ...
}, {
freezeTableName: true,
timestamps: false,
tableName: 'trackers'
});
Tracker.associate = function(models) {
Tracker.belongsTo(models.User, {
foreignKey: 'userId'
});
Tracker.belongsToMany(models.Link, {
as: 'links',
through: models.LinkTrackers,
foreignKey: 'trackerId',
targetKey: 'id'
});
};
return Tracker;
};
观察:
对于两个关联模型,创建的 SQL 查询似乎也是相同的。这是给定查询的结果 SQL
SELECT `Link`.*,
`trackers`.`id` AS `trackers.id`,
`trackers`.`createdat` AS `trackers.createdAt`,
`trackers`.`name` AS `trackers.name`,
`trackers`.`vendor` AS `trackers.vendor`,
`trackers`.`vendortrackerid` AS `trackers.vendorTrackerId`,
`trackers`.`userid` AS `trackers.userId`,
`trackers->LinkTrackers`.`linkid` AS `trackers.LinkTrackers.linkId`,
`trackers->LinkTrackers`.`trackerid` AS `trackers.LinkTrackers.trackerId`
,
`overlays`.`id` AS `overlays.id`,
`overlays`.`name` AS `overlays.name`,
`overlays`.`type` AS `overlays.type`,
`overlays`.`config` AS `overlays.config`,
`overlays`.`userid` AS `overlays.userId`,
`overlays`.`createdat` AS `overlays.createdAt`,
`overlays`.`updatedat` AS `overlays.updatedAt`,
`overlays->LinkOverlays`.`linkid` AS `overlays.LinkOverlays.linkId`,
`overlays->LinkOverlays`.`overlayid` AS `overlays.LinkOverlays.overlayId`
FROM (SELECT `Link`.`id`,
`Link`.`name`,
`Link`.`originalurl`,
`Link`.`code`,
`Link`.`type`,
`Link`.`userid`,
`Link`.`hitcount`,
`Link`.`opengraph`,
`Link`.`createdat`,
`Link`.`updatedat`
FROM `links` AS `Link`
WHERE `Link`.`userid` = 1
ORDER BY `Link`.`createdat` DESC
LIMIT 0, 10) AS `Link`
LEFT OUTER JOIN ( `link_trackers` AS `trackers->LinkTrackers`
INNER JOIN `trackers` AS `trackers`
ON `trackers`.`id` =
`trackers->LinkTrackers`.`trackerid`)
ON `Link`.`id` = `trackers->LinkTrackers`.`trackerid`
LEFT OUTER JOIN ( `link_overlays` AS `overlays->LinkOverlays`
INNER JOIN `overlays` AS `overlays`
ON `overlays`.`id` =
`overlays->LinkOverlays`.`overlayid`)
ON `Link`.`id` = `overlays->LinkOverlays`.`linkid`
ORDER BY `Link`.`createdat` DESC;
更多信息
下面是两个关系表模型
LinkOverlays.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const LinkOverlays = sequelize.define('LinkOverlays', {
linkId: {
type: DataTypes.INTEGER,
primaryKey: true,
unique: 'linkIdOverlayIdComposite'
},
overlayId: {
type: DataTypes.INTEGER,
primaryKey: true,
unique: 'linkIdOverlayIdComposite'
}
}, {
freezeTableName: true,
timestamps: false,
tableName: 'link_overlays'
});
LinkOverlays.associate = function(models) {
// associations can be defined here
LinkOverlays.belongsTo(models.Link, {
foreignKey: 'linkId',
through: models.LinkOverlays,
targetKey: 'id'
});
// associations can be defined here
LinkOverlays.belongsTo(models.Overlay, {
foreignKey: 'overlayId',
through: models.LinkOverlays,
targetKey: 'id'
});
};
return LinkOverlays;
};
LinkTrackers.js
'use strict';
module.exports = (sequelize, DataTypes) => {
const LinkTrackers = sequelize.define('LinkTrackers', {
linkId: {
type: DataTypes.INTEGER,
primaryKey: true,
unique: 'linkIdTrackerIdComposite'
},
trackerId: {
type: DataTypes.INTEGER,
primaryKey: true,
unique: 'linkIdTrackerIdComposite'
}
}, {
freezeTableName: true,
timestamps: false,
tableName: 'link_trackers'
});
LinkTrackers.associate = function(models) {
LinkTrackers.belongsTo(models.Link, {
foreignKey: 'linkId',
through: models.LinkTrackers,
targetKey: 'id'
});
LinkTrackers.belongsTo(models.Tracker, {
foreignKey: 'trackerId',
through: models.LinkTrackers,
targetKey: 'id'
});
};
return LinkTrackers;
};
我已经尝试了很多小时来追查这个问题,但失败了。最令人费解的是一个在工作(Overlay
)而另一个不在(Tracker
)
解决方案
在你的
链接.js
Link.belongsToMany(models.Overlay, {
as: 'overlays',
through: models.LinkOverlays,
foreignKey: 'linkId',
targetKey: 'id',
});
Link.belongsToMany(models.Tracker, {
as: 'trackers',
through: models.LinkTrackers,
foreignKey: 'trackerId', // should be linkId
targetKey: 'id',
});
与的关系LinkOverlays
是通过,linkId
但与的关系也应该是LinkTrackers
通过trackerId
linkId
推荐阅读
- nestjs - 使用带有 Code-First 方法的 NestJS GraphQL 插件检测字段类型的问题
- c# - 为什么我会收到与 WebClient 不兼容的浏览器消息?
- apache-kafka-streams - Kafka Streams DSL:将 KStream 聚合到 GlobalKTable
- javascript - 将 Blob 对象从 Angular 应用 Chrome 浏览器拖放到 windows
- python-3.x - 在 Flask 中解析 GET 请求正文?
- apache-spark - PySpark to_utc_timestamp 返回同一时间
- sql-server - 在 azure 函数 powershell 中使用 bcp
- c# - 当 FTP 服务器上有大量文件时,FtpWebResponse 抛出异常代码 550
- css - 从父组件角度更改子组件样式但不是全局更改
- webpack - Aurelia2 的哪个模块加载器?