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

2024年7月25日 · 阅读需 10 分钟

全栈开发者

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

前言

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

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

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

关联关系类型

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

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

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

// 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. 评论系统（自关联 + 多层关联）

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

2. 用户关注系统

// 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. 复杂的关联查询

// 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. 聚合查询示例

// 用户统计服务
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 的模型设计和关联系统：

✅ 关联关系类型：

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

✅ 复杂关联示例：

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

✅ 高级查询技巧：

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

✅ 最佳实践：

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

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

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

相关文章推荐：

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

前言​

关联关系类型​

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

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

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

复杂关联示例​

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

2. 用户关注系统​

高级查询技巧​

1. 复杂的关联查询​

2. 聚合查询示例​

总结​

前言