Feathers.js + Sequelize 模型设计与关联 - 构建复杂的数据关系

发布时间：2024-07-25
作者：一介布衣
标签：Feathers.js, Sequelize, 模型关联, 数据关系, ORM

前言

上一篇文章我们学习了 Feathers.js 与 Sequelize 的基础集成，今天咱们来深入学习模型设计和关联关系。说实话，关系型数据库的精髓就在于表之间的关联，而 Sequelize 在这方面做得特别好，它提供了非常直观的 API 来定义和操作各种关联关系。

我记得刚开始设计数据库的时候，总是搞不清楚一对一、一对多、多对多的区别，更别说在代码中如何实现了。后来用了 Sequelize，发现原来可以这么简单优雅地处理复杂的数据关系。

今天我就带大家深入学习 Sequelize 的模型设计和关联系统，让你能够构建出结构清晰、关系明确的数据模型。

关联关系类型

1. 一对一关联 (One-to-One)

javascript

// src/models/users.model.js - 用户模型
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const { DataTypes } = require('sequelize');
  
  const users = sequelizeClient.define('users', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    username: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {
    tableName: 'users',
    timestamps: true,
    underscored: true
  });

  // 关联定义
  users.associate = function(models) {
    // 一对一：用户 -> 用户资料
    users.hasOne(models.userProfiles, {
      foreignKey: 'userId',
      as: 'profile',
      onDelete: 'CASCADE'
    });
  };

  return users;
};

// src/models/user-profiles.model.js - 用户资料模型
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const { DataTypes } = require('sequelize');
  
  const userProfiles = sequelizeClient.define('userProfiles', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    
    userId: {
      type: DataTypes.UUID,
      allowNull: false,
      unique: true,
      field: 'user_id',
      references: {
        model: 'users',
        key: 'id'
      }
    },
    
    firstName: {
      type: DataTypes.STRING,
      field: 'first_name'
    },
    
    lastName: {
      type: DataTypes.STRING,
      field: 'last_name'
    },
    
    avatar: DataTypes.STRING,
    bio: DataTypes.TEXT,
    
    birthDate: {
      type: DataTypes.DATEONLY,
      field: 'birth_date'
    },
    
    phone: DataTypes.STRING,
    website: DataTypes.STRING,
    
    // 地址信息
    country: DataTypes.STRING,
    city: DataTypes.STRING,
    address: DataTypes.TEXT,
    postalCode: {
      type: DataTypes.STRING,
      field: 'postal_code'
    },
    
    // 社交媒体
    socialLinks: {
      type: DataTypes.JSON,
      field: 'social_links',
      defaultValue: {}
    }
  }, {
    tableName: 'user_profiles',
    timestamps: true,
    underscored: true
  });

  // 关联定义
  userProfiles.associate = function(models) {
    // 反向关联：用户资料 -> 用户
    userProfiles.belongsTo(models.users, {
      foreignKey: 'userId',
      as: 'user'
    });
  };

  return userProfiles;
};

2. 一对多关联 (One-to-Many)

javascript

// src/models/categories.model.js - 分类模型
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const { DataTypes } = require('sequelize');
  
  const categories = sequelizeClient.define('categories', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    
    name: {
      type: DataTypes.STRING,
      allowNull: false
    },
    
    slug: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    
    description: DataTypes.TEXT,
    color: DataTypes.STRING,
    icon: DataTypes.STRING,
    
    sortOrder: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'sort_order'
    },
    
    isActive: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
      field: 'is_active'
    },
    
    // 层级结构支持
    parentId: {
      type: DataTypes.UUID,
      field: 'parent_id',
      references: {
        model: 'categories',
        key: 'id'
      }
    },
    
    level: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    
    path: DataTypes.STRING,  // 存储层级路径
    
    // 统计信息
    postsCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'posts_count'
    }
  }, {
    tableName: 'categories',
    timestamps: true,
    underscored: true,
    
    indexes: [
      { fields: ['slug'] },
      { fields: ['parent_id'] },
      { fields: ['is_active'] },
      { fields: ['sort_order'] }
    ]
  });

  // 关联定义
  categories.associate = function(models) {
    // 一对多：分类 -> 文章
    categories.hasMany(models.posts, {
      foreignKey: 'categoryId',
      as: 'posts'
    });
    
    // 自关联：父分类 -> 子分类
    categories.hasMany(categories, {
      foreignKey: 'parentId',
      as: 'children'
    });
    
    // 反向自关联：子分类 -> 父分类
    categories.belongsTo(categories, {
      foreignKey: 'parentId',
      as: 'parent'
    });
  };

  // 实例方法
  categories.prototype.getAncestors = async function() {
    const ancestors = [];
    let current = this;
    
    while (current.parentId) {
      current = await categories.findByPk(current.parentId);
      if (current) {
        ancestors.unshift(current);
      } else {
        break;
      }
    }
    
    return ancestors;
  };

  categories.prototype.getDescendants = async function() {
    const descendants = [];
    
    const findChildren = async (parentId) => {
      const children = await categories.findAll({
        where: { parentId }
      });
      
      for (const child of children) {
        descendants.push(child);
        await findChildren(child.id);
      }
    };
    
    await findChildren(this.id);
    return descendants;
  };

  // 类方法
  categories.buildTree = async function(parentId = null) {
    const categories = await this.findAll({
      where: { parentId },
      order: [['sortOrder', 'ASC'], ['name', 'ASC']]
    });
    
    for (const category of categories) {
      category.dataValues.children = await this.buildTree(category.id);
    }
    
    return categories;
  };

  return categories;
};

// src/models/posts.model.js - 文章模型（关联部分）
posts.associate = function(models) {
  // 多对一：文章 -> 分类
  posts.belongsTo(models.categories, {
    foreignKey: 'categoryId',
    as: 'category'
  });
  
  // 多对一：文章 -> 作者
  posts.belongsTo(models.users, {
    foreignKey: 'authorId',
    as: 'author'
  });
};

3. 多对多关联 (Many-to-Many)

javascript

// src/models/tags.model.js - 标签模型
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const { DataTypes } = require('sequelize');
  
  const tags = sequelizeClient.define('tags', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    
    slug: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true
    },
    
    description: DataTypes.TEXT,
    color: DataTypes.STRING,
    
    postsCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'posts_count'
    }
  }, {
    tableName: 'tags',
    timestamps: true,
    underscored: true,
    
    indexes: [
      { fields: ['slug'] },
      { fields: ['name'] }
    ]
  });

  // 关联定义
  tags.associate = function(models) {
    // 多对多：标签 &lt;-&gt; 文章
    tags.belongsToMany(models.posts, {
      through: 'post_tags',
      foreignKey: 'tagId',
      otherKey: 'postId',
      as: 'posts'
    });
  };

  return tags;
};

// src/models/post-tags.model.js - 中间表模型
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const { DataTypes } = require('sequelize');
  
  const postTags = sequelizeClient.define('postTags', {
    postId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'post_id',
      references: {
        model: 'posts',
        key: 'id'
      }
    },
    
    tagId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'tag_id',
      references: {
        model: 'tags',
        key: 'id'
      }
    },
    
    // 额外的中间表字段
    order: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    
    addedBy: {
      type: DataTypes.UUID,
      field: 'added_by',
      references: {
        model: 'users',
        key: 'id'
      }
    }
  }, {
    tableName: 'post_tags',
    timestamps: true,
    underscored: true,
    
    indexes: [
      {
        unique: true,
        fields: ['post_id', 'tag_id']
      },
      { fields: ['post_id'] },
      { fields: ['tag_id'] }
    ]
  });

  return postTags;
};

// 文章模型中的多对多关联
posts.associate = function(models) {
  // 多对多：文章 &lt;-&gt; 标签
  posts.belongsToMany(models.tags, {
    through: models.postTags,
    foreignKey: 'postId',
    otherKey: 'tagId',
    as: 'tags'
  });
};

复杂关联示例

1. 评论系统（自关联 + 多层关联）

javascript

// src/models/comments.model.js
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const { DataTypes } = require('sequelize');
  
  const comments = sequelizeClient.define('comments', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true
    },
    
    content: {
      type: DataTypes.TEXT,
      allowNull: false
    },
    
    // 关联字段
    postId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'post_id',
      references: {
        model: 'posts',
        key: 'id'
      }
    },
    
    authorId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'author_id',
      references: {
        model: 'users',
        key: 'id'
      }
    },
    
    // 自关联：回复评论
    parentId: {
      type: DataTypes.UUID,
      field: 'parent_id',
      references: {
        model: 'comments',
        key: 'id'
      }
    },
    
    // 层级信息
    level: {
      type: DataTypes.INTEGER,
      defaultValue: 0
    },
    
    path: DataTypes.STRING,  // 存储评论路径
    
    // 状态
    status: {
      type: DataTypes.ENUM('pending', 'approved', 'rejected'),
      defaultValue: 'pending'
    },
    
    isDeleted: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
      field: 'is_deleted'
    },
    
    // 统计
    likesCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'likes_count'
    },
    
    repliesCount: {
      type: DataTypes.INTEGER,
      defaultValue: 0,
      field: 'replies_count'
    }
  }, {
    tableName: 'comments',
    timestamps: true,
    underscored: true,
    
    indexes: [
      { fields: ['post_id'] },
      { fields: ['author_id'] },
      { fields: ['parent_id'] },
      { fields: ['status'] },
      { fields: ['created_at'] }
    ],
    
    scopes: {
      approved: {
        where: {
          status: 'approved',
          isDeleted: false
        }
      },
      
      topLevel: {
        where: {
          parentId: null
        }
      }
    }
  });

  // 关联定义
  comments.associate = function(models) {
    // 多对一：评论 -> 文章
    comments.belongsTo(models.posts, {
      foreignKey: 'postId',
      as: 'post'
    });
    
    // 多对一：评论 -> 作者
    comments.belongsTo(models.users, {
      foreignKey: 'authorId',
      as: 'author'
    });
    
    // 自关联：父评论 -> 子评论
    comments.hasMany(comments, {
      foreignKey: 'parentId',
      as: 'replies'
    });
    
    // 反向自关联：子评论 -> 父评论
    comments.belongsTo(comments, {
      foreignKey: 'parentId',
      as: 'parent'
    });
    
    // 多对多：评论点赞
    comments.belongsToMany(models.users, {
      through: 'comment_likes',
      foreignKey: 'commentId',
      otherKey: 'userId',
      as: 'likedBy'
    });
  };

  // 实例方法
  comments.prototype.getRepliesTree = async function() {
    const replies = await comments.findAll({
      where: { parentId: this.id },
      include: ['author'],
      order: [['createdAt', 'ASC']]
    });
    
    for (const reply of replies) {
      reply.dataValues.replies = await reply.getRepliesTree();
    }
    
    return replies;
  };

  // 类方法
  comments.buildCommentsTree = async function(postId) {
    const topLevelComments = await this.scope('approved', 'topLevel').findAll({
      where: { postId },
      include: ['author'],
      order: [['createdAt', 'ASC']]
    });
    
    for (const comment of topLevelComments) {
      comment.dataValues.replies = await comment.getRepliesTree();
    }
    
    return topLevelComments;
  };

  return comments;
};

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176

2. 用户关注系统

javascript

// src/models/user-follows.model.js
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const { DataTypes } = require('sequelize');
  
  const userFollows = sequelizeClient.define('userFollows', {
    followerId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'follower_id',
      references: {
        model: 'users',
        key: 'id'
      }
    },
    
    followingId: {
      type: DataTypes.UUID,
      allowNull: false,
      field: 'following_id',
      references: {
        model: 'users',
        key: 'id'
      }
    },
    
    // 关注类型
    type: {
      type: DataTypes.ENUM('follow', 'block', 'mute'),
      defaultValue: 'follow'
    },
    
    // 通知设置
    notifications: {
      type: DataTypes.BOOLEAN,
      defaultValue: true
    }
  }, {
    tableName: 'user_follows',
    timestamps: true,
    underscored: true,
    
    indexes: [
      {
        unique: true,
        fields: ['follower_id', 'following_id']
      },
      { fields: ['follower_id'] },
      { fields: ['following_id'] },
      { fields: ['type'] }
    ],
    
    validate: {
      notSelfFollow() {
        if (this.followerId === this.followingId) {
          throw new Error('用户不能关注自己');
        }
      }
    }
  });

  return userFollows;
};

// 用户模型中的关注关联
users.associate = function(models) {
  // 用户关注的人
  users.belongsToMany(users, {
    through: models.userFollows,
    as: 'following',
    foreignKey: 'followerId',
    otherKey: 'followingId'
  });
  
  // 关注用户的人
  users.belongsToMany(users, {
    through: models.userFollows,
    as: 'followers',
    foreignKey: 'followingId',
    otherKey: 'followerId'
  });
  
  // 直接访问关注关系
  users.hasMany(models.userFollows, {
    foreignKey: 'followerId',
    as: 'followingRelations'
  });
  
  users.hasMany(models.userFollows, {
    foreignKey: 'followingId',
    as: 'followerRelations'
  });
};

高级查询技巧

1. 复杂的关联查询

javascript

// src/services/posts/posts.class.js
class PostsService extends Service {
  async find(params) {
    const { query = {} } = params;
    
    const sequelizeOptions = {
      include: [
        // 包含作者信息
        {
          model: this.app.service('users').Model,
          as: 'author',
          attributes: ['id', 'username', 'firstName', 'lastName', 'avatar'],
          include: [
            {
              model: this.app.service('userProfiles').Model,
              as: 'profile',
              attributes: ['bio', 'website']
            }
          ]
        },
        
        // 包含分类信息
        {
          model: this.app.service('categories').Model,
          as: 'category',
          attributes: ['id', 'name', 'slug', 'color']
        },
        
        // 包含标签信息
        {
          model: this.app.service('tags').Model,
          as: 'tags',
          attributes: ['id', 'name', 'slug', 'color'],
          through: { attributes: [] }  // 不包含中间表字段
        },
        
        // 包含评论统计
        {
          model: this.app.service('comments').Model,
          as: 'comments',
          attributes: [],
          where: { status: 'approved' },
          required: false
        }
      ],
      
      // 添加计算字段
      attributes: {
        include: [
          [
            this.Model.sequelize.fn('COUNT', 
              this.Model.sequelize.col('comments.id')
            ),
            'commentsCount'
          ]
        ]
      },
      
      group: ['posts.id', 'author.id', 'author->profile.id', 'category.id', 'tags.id'],
      order: [['publishedAt', 'DESC']]
    };

    // 按分类筛选
    if (query.category) {
      sequelizeOptions.include[1].where = { slug: query.category };
      delete query.category;
    }

    // 按标签筛选
    if (query.tags) {
      const tags = Array.isArray(query.tags) ? query.tags : [query.tags];
      sequelizeOptions.include[2].where = { slug: { [Op.in]: tags } };
      sequelizeOptions.having = this.Model.sequelize.where(
        this.Model.sequelize.fn('COUNT', this.Model.sequelize.col('tags.id')),
        tags.length
      );
      delete query.tags;
    }

    // 按作者筛选
    if (query.author) {
      sequelizeOptions.include[0].where = { username: query.author };
      delete query.author;
    }

    params.sequelize = sequelizeOptions;
    
    return super.find(params);
  }

  // 获取热门文章
  async getPopularPosts(params = {}) {
    const { limit = 10, days = 7 } = params;
    const startDate = new Date(Date.now() - days * 24 * 60 * 60 * 1000);
    
    return this.Model.findAll({
      where: {
        status: 'published',
        publishedAt: { [Op.gte]: startDate }
      },
      include: [
        {
          model: this.app.service('users').Model,
          as: 'author',
          attributes: ['username', 'firstName', 'lastName', 'avatar']
        },
        {
          model: this.app.service('categories').Model,
          as: 'category',
          attributes: ['name', 'slug', 'color']
        }
      ],
      attributes: {
        include: [
          [
            this.Model.sequelize.literal(
              '(view_count + like_count * 5 + comment_count * 10)'
            ),
            'popularityScore'
          ]
        ]
      },
      order: [
        [this.Model.sequelize.literal('popularityScore'), 'DESC'],
        ['publishedAt', 'DESC']
      ],
      limit
    });
  }

  // 获取相关文章
  async getRelatedPosts(postId, params = {}) {
    const { limit = 5 } = params;
    
    // 获取当前文章的标签
    const currentPost = await this.Model.findByPk(postId, {
      include: ['tags']
    });
    
    if (!currentPost || !currentPost.tags.length) {
      return [];
    }
    
    const tagIds = currentPost.tags.map(tag => tag.id);
    
    return this.Model.findAll({
      where: {
        id: { [Op.ne]: postId },
        status: 'published'
      },
      include: [
        {
          model: this.app.service('tags').Model,
          as: 'tags',
          where: { id: { [Op.in]: tagIds } },
          through: { attributes: [] }
        },
        {
          model: this.app.service('users').Model,
          as: 'author',
          attributes: ['username', 'firstName', 'lastName', 'avatar']
        }
      ],
      attributes: {
        include: [
          [
            this.Model.sequelize.fn('COUNT', 
              this.Model.sequelize.col('tags.id')
            ),
            'matchingTags'
          ]
        ]
      },
      group: ['posts.id', 'author.id'],
      order: [
        [this.Model.sequelize.literal('matchingTags'), 'DESC'],
        ['publishedAt', 'DESC']
      ],
      limit
    });
  }
}

2. 聚合查询示例

javascript

// 用户统计服务
class UserStatsService {
  constructor(app) {
    this.app = app;
    this.User = app.service('users').Model;
    this.Post = app.service('posts').Model;
    this.Comment = app.service('comments').Model;
  }

  // 获取用户详细统计
  async getUserDetailedStats(userId) {
    const stats = await this.User.findByPk(userId, {
      include: [
        {
          model: this.Post,
          as: 'posts',
          attributes: [],
          where: { status: 'published' },
          required: false
        },
        {
          model: this.Comment,
          as: 'comments',
          attributes: [],
          where: { status: 'approved' },
          required: false
        }
      ],
      attributes: [
        'id',
        'username',
        [this.User.sequelize.fn('COUNT', this.User.sequelize.col('posts.id')), 'postsCount'],
        [this.User.sequelize.fn('COUNT', this.User.sequelize.col('comments.id')), 'commentsCount'],
        [this.User.sequelize.fn('SUM', this.User.sequelize.col('posts.view_count')), 'totalViews'],
        [this.User.sequelize.fn('SUM', this.User.sequelize.col('posts.like_count')), 'totalLikes']
      ],
      group: ['users.id']
    });

    return stats;
  }

  // 获取活跃用户排行
  async getActiveUsersRanking(params = {}) {
    const { limit = 10, days = 30 } = params;
    const startDate = new Date(Date.now() - days * 24 * 60 * 60 * 1000);
    
    return this.User.findAll({
      include: [
        {
          model: this.Post,
          as: 'posts',
          attributes: [],
          where: {
            status: 'published',
            createdAt: { [Op.gte]: startDate }
          },
          required: false
        },
        {
          model: this.Comment,
          as: 'comments',
          attributes: [],
          where: {
            status: 'approved',
            createdAt: { [Op.gte]: startDate }
          },
          required: false
        }
      ],
      attributes: [
        'id',
        'username',
        'firstName',
        'lastName',
        'avatar',
        [this.User.sequelize.fn('COUNT', this.User.sequelize.col('posts.id')), 'recentPosts'],
        [this.User.sequelize.fn('COUNT', this.User.sequelize.col('comments.id')), 'recentComments'],
        [
          this.User.sequelize.literal(
            '(COUNT(posts.id) * 10 + COUNT(comments.id) * 2)'
          ),
          'activityScore'
        ]
      ],
      group: ['users.id'],
      order: [
        [this.User.sequelize.literal('activityScore'), 'DESC'],
        ['createdAt', 'ASC']
      ],
      limit
    });
  }
}

总结

通过这篇文章，我们深入学习了 Sequelize 的模型设计和关联系统：

✅ 关联关系类型：

一对一关联的设计和实现
一对多关联和层级结构
多对多关联和中间表

✅ 复杂关联示例：

评论系统的自关联
用户关注系统设计
多层级数据结构

✅ 高级查询技巧：

复杂的关联查询
聚合统计查询
性能优化策略

✅ 最佳实践：

模型设计原则
关联定义规范
查询优化技巧

掌握了这些知识，你就能够设计出结构清晰、关系明确的复杂数据模型，并高效地进行各种查询操作。

下一篇文章，我们将学习高级查询和性能优化。

相关文章推荐：

有问题欢迎留言讨论，我会及时回复大家！

🧭 快速导航

Feathers.js + Sequelize 模型设计与关联 - 构建复杂的数据关系 ​

前言 ​

关联关系类型 ​

1. 一对一关联 (One-to-One) ​

2. 一对多关联 (One-to-Many) ​

3. 多对多关联 (Many-to-Many) ​

复杂关联示例 ​

1. 评论系统（自关联 + 多层关联） ​

2. 用户关注系统 ​

高级查询技巧 ​

1. 复杂的关联查询 ​

2. 聚合查询示例 ​

总结 ​

Feathers.js + Sequelize 模型设计与关联 - 构建复杂的数据关系

前言

关联关系类型

1. 一对一关联 (One-to-One)

2. 一对多关联 (One-to-Many)

3. 多对多关联 (Many-to-Many)

复杂关联示例

1. 评论系统（自关联 + 多层关联）

2. 用户关注系统

高级查询技巧

1. 复杂的关联查询

2. 聚合查询示例

总结