如何在sequelize中显示m:m关系

Question

我正在构建博客nodejs api。我有诸如Post、Category和PostCategory之类的表格。

所以，我想从PostCategory查询数据。

const Post = sequelize.define('post', {
id:  {
type: DataTypes.INTEGER,
autoIncrement: true,
allowNull: false,
primaryKey: true,
},
content: {
type: DataTypes.TEXT,
allowNull: false,
},
photo: {
type: DataTypes.STRING,
allowNull: false
}
})

const Category = sequelize.define('category', {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
allowNull: false,
primaryKey: true,
},
label: {
type: DataTypes.INTEGER,
allowNull: false
}
})

以下是我的联想：

const PostCategory = sequelize.define('postcategory', {
postId: {
type: DataTypes.INTEGER,
references: {
model: Post,
key: 'id',
}
},
categoryId: {
type: DataTypes.INTEGER,
references: {
model: Category,
key: 'id',
}
}
})
Post.belongsToMany(Category, { through: PostCategory })
Category.belongsToMany(Post, { through: PostCategory })

问题是：；如何显示以显示帖子及其类别"我试过：

const posts = await PostCategory.findAll({
include: [{
model: Post
}]
}).then(post => response.send(post))

但采取了回应：{"名称"："SequelizeEagleLoadingError"}

Answer 1

您应该使用Post.findAll({ include: [{ model: Category }] })来获取所有帖子及其关联类别，而不是使用联接表PostCategory。

下面是一个使用"sequelize": "^5.21.3":的工作示例

import { sequelize } from '../../db';
import { DataTypes } from 'sequelize';
const Post = sequelize.define('post', {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
allowNull: false,
primaryKey: true,
},
content: {
type: DataTypes.TEXT,
allowNull: false,
},
photo: {
type: DataTypes.STRING,
allowNull: false,
},
});
const Category = sequelize.define('category', {
id: {
type: DataTypes.INTEGER,
autoIncrement: true,
allowNull: false,
primaryKey: true,
},
label: {
type: DataTypes.INTEGER,
allowNull: false,
},
});
const PostCategory = sequelize.define('postcategory', {
postId: {
type: DataTypes.INTEGER,
references: {
model: Post,
key: 'id',
},
},
categoryId: {
type: DataTypes.INTEGER,
references: {
model: Category,
key: 'id',
},
},
});
(Post as any).belongsToMany(Category, { through: PostCategory });
(Category as any).belongsToMany(Post, { through: PostCategory });
(async function test() {
try {
await sequelize.sync({ force: true });
// seed
await (Post as any).bulkCreate(
[
{ content: 'post 1', photo: 'photo 1', categories: [{ label: 1 }, { label: 2 }] },
{ content: 'post 2', photo: 'photo 2', categories: [{ label: 3 }, { label: 4 }] },
],
{ include: [Category] },
);
const posts = await (Post as any).findAll({
include: [{ model: Category }],
raw: true,
});
console.log(posts);
} catch (error) {
console.log(error);
} finally {
await sequelize.close();
}
})();

打印posts:

[
{
id: 1,
content: 'post 1',
photo: 'photo 1',
'categories.id': 1,
'categories.label': 1,
'categories.postcategory.postId': 1,
'categories.postcategory.categoryId': 1
},
{
id: 1,
content: 'post 1',
photo: 'photo 1',
'categories.id': 2,
'categories.label': 2,
'categories.postcategory.postId': 1,
'categories.postcategory.categoryId': 2
},
{
id: 2,
content: 'post 2',
photo: 'photo 2',
'categories.id': 3,
'categories.label': 3,
'categories.postcategory.postId': 2,
'categories.postcategory.categoryId': 3
},
{
id: 2,
content: 'post 2',
photo: 'photo 2',
'categories.id': 4,
'categories.label': 4,
'categories.postcategory.postId': 2,
'categories.postcategory.categoryId': 4
}
]

数据库中的数据记录：

node-sequelize-examples=# select * from post;
id | content |  photo  
----+---------+---------
1 | post 1  | photo 1
2 | post 2  | photo 2
(2 rows)
node-sequelize-examples=# select * from category;
id | label 
----+-------
1 |     1
2 |     2
3 |     3
4 |     4
(4 rows)
node-sequelize-examples=# select * from postcategory;
postId | categoryId 
--------+------------
1 |          1
1 |          2
2 |          3
2 |          4
(4 rows)