Node系列 · ORM数据查询真实业务里 90% 的查询是跨多表 JOIN——查用户时连带查订单、查文章时连带查作者和评论。Sequelize 的关联association 预加载include让这件事用 JS 方法完成不必手写 JOIN。一、模型关联复习上一章 《Sequelize 模型》 定义了四种关联// 一对多User 有多个 Post User.hasMany(Post, { foreignKey: userId }); Post.belongsTo(User, { foreignKey: userId }); // 一对一User 有一个 Profile User.hasOne(Profile, { foreignKey: userId }); Profile.belongsTo(User, { foreignKey: userId }); // 多对多Post 有多个 Tag Post.belongsToMany(Tag, { through: PostTags }); Tag.belongsToMany(Post, { through: PostTags });定义关联后Sequelize 自动给模型添加 4 个方法方法含义user.getPosts()查 user 的所有 postuser.setPosts(arr)设置 user 的 post多对多user.addPost(p)加一条 post 给 useruser.countPosts()统计 post 数二、预加载Include2.1 一层 include// 查所有 Post附带作者信息 const posts await Post.findAll({ include: [{ model: User, as: author, // 别名belongsTo 时可选 attributes: [id, name], // 只取需要的字段 }], }); console.log(posts[0].author.name); // 直接访问关联对象生成的 SQLSELECTPost.*,author.id,author.nameFROMpostsASPostLEFTJOINusersASauthorONauthor.idPost.userId;2.2 嵌套 include// 查所有 Post附带作者 作者的 Profile 文章的评论 const posts await Post.findAll({ include: [ { model: User, as: author, include: [{ model: Profile }] }, { model: Comment, include: [{ model: User, as: commenter }] }, ], });2.3 多对多 include// 查所有 Post附带 tags const posts await Post.findAll({ include: [{ model: Tag, through: { attributes: [] } }], // 不取中间表字段 });through: { attributes: [] }排除中间表的字段避免结果里多出冗余列。2.4 include where// 查所有 Post附带作者只查已发布的 const posts await Post.findAll({ where: { status: published }, include: [{ model: User, as: author }], }); // include 里也可以加 where过滤关联表 const users await User.findAll({ include: [{ model: Post, where: { status: published }, // 只 include 已发布的文章 required: false, // LEFT JOIN默认 false }], });requiredSQL 类型行为false默认LEFT JOIN即使没匹配也保留主表行trueINNER JOIN没匹配就过滤掉主表行三、复杂条件查询3.1 关联过滤嵌套 where// 找出至少有一篇已发布文章的用户 const users await User.findAll({ include: [{ model: Post, where: { status: published }, required: true, }], });3.2 关联排序order 含关联// 按最新文章排序用户 const users await User.findAll({ order: [[{ model: Post, as: posts }, createdAt, DESC]], include: [{ model: Post, as: posts }], });四、聚合与分组4.1 计数// 查 Post 列表附带评论数 const posts await Post.findAll({ attributes: { include: [[Sequelize.fn(COUNT, Sequelize.col(comments.id)), commentCount]], }, include: [{ model: Comment, attributes: [] }], // 不返回 comments 数据 group: [Post.id], order: [[Sequelize.literal(commentCount), DESC]], });include attributes: []是只 JOIN 不取数据的写法——只为了拿到关联表做聚合。4.2 findAndCountAll 分页const { count, rows } await Post.findAndCountAll({ where: { status: published }, include: [{ model: User, as: author, attributes: [id, name] }], distinct: true, // count 时去重避免 JOIN 重复计算 limit: 20, offset: 0, order: [[createdAt, DESC]], }); console.log(共 ${count} 条当前页 ${rows.length} 条);::: warningfindAndCountAllinclude时必须distinct: true否则 JOIN 出来的行数会虚高比如一篇文章 5 条评论会被算成 5 篇文章。:::五、原生 SQLRaw QueriesORM 不万能。复杂报表查询用sequelize.query()const [results, metadata] await sequelize.query( SELECT u.id, u.name, COUNT(DISTINCT p.id) AS post_count, COUNT(DISTINCT c.id) AS comment_count FROM users u LEFT JOIN posts p ON p.user_id u.id LEFT JOIN comments c ON c.post_id p.id WHERE u.created_at ? GROUP BY u.id ORDER BY post_count DESC LIMIT ?, { replacements: [oneMonthAgo, 10], type: sequelize.QueryTypes.SELECT, } );type含义SELECT返回数组INSERT/UPDATE/DELETE返回受影响行数BULKUPDATE/BULKDELETE批量操作RAW默认返回原始数据replacements是占位符?自动转义——比手拼 SQL 安全。六、查询优化清单优化点做法避免SELECT *显式attributes: [id, name, ...]限制关联深度include 不要超过 3 层太多就拆查询分页必备distinctfindAndCountAll include 必须distinct: trueN1 查询用include一次性预加载不要for user of users { await user.getPosts() }大结果集limit/offset限制用游标分页替代 offset复杂报表sequelize.query()跑 raw SQL6.1 N1 反例 vs 正例// ❌ N11 次查 usersN 次查 posts const users await User.findAll(); for (const user of users) { user.posts await user.getPosts(); // 每次都查数据库 } const usersWithPosts await User.findAll({ include: [{ model: Post }], // 1 次查询拿全 });七、完整案例async function getPublishedPosts({ page 1, pageSize 20, keyword }) { const { count, rows } await Post.findAndCountAll({ where: { status: published, ...(keyword { title: { [Op.like]: %${keyword}% } }), }, include: [ { model: User, as: author, attributes: [id, name, avatar] }, { model: Tag, through: { attributes: [] } }, ], attributes: { include: [[Sequelize.fn(COUNT, Sequelize.col(comments.id)), commentCount]], }, group: [Post.id, author.id, Tags.id], distinct: true, limit: pageSize, offset: (page - 1) * pageSize, order: [[createdAt, DESC]], }); return { list: rows, total: count.length, // COUNT(DISTINCT Post.id) page, pageSize, }; }八、最佳实践场景推荐一层 / 二层关联includeattributes精准控制深度 3 层拆查询后应用层拼装列表分页findAndCountAlldistinct: true关联计数attributes.include fn(COUNT)复杂报表sequelize.query()跑 raw SQLN1永远用include预加载大字段attributes.exclude排除TEXT/JSON字段九、小结关联查询三步定义hasMany/belongsTo等 →include预加载 →attributes精准控制字段findAndCountAllinclude必须distinct: true避免 JOIN 重复计算关联排序order: [[{ model: Post, as: posts }, createdAt, DESC]]关联计数attributes.include fn(COUNT, col(comments.id))复杂报表用sequelize.query()跑 raw SQLreplacements占位符防注入永远避免 N1用include一次拿全不要循环调user.getPosts()