一介布衣

🧭 快速导航

Sequelize 查询方法详解 - 复杂查询条件和操作符使用 ​

发布时间：2024-02-19
作者：一介布衣
标签：Sequelize, 高级查询, 操作符, 复杂查询

前言 ​

上一篇文章我们学习了 Sequelize 的基础查询操作，今天咱们来深入学习更复杂的查询方法和技巧。说实话，掌握了这些高级查询技巧，基本上就能应对大部分复杂的业务场景了。

我记得刚开始做项目的时候，遇到复杂的查询需求总是不知道怎么用 Sequelize 实现，经常要写原生 SQL。后来慢慢摸索，发现 Sequelize 的查询功能其实非常强大，几乎所有的 SQL 查询都能用它来实现。

今天我就把这些高级查询技巧分享给大家，让大家能够用 Sequelize 写出优雅而高效的查询代码。

高级操作符详解 ​

1. 字符串操作符 ​

javascript
const { Op } = require('sequelize');

// 模糊匹配
const users = await User.findAll({
  where: {
    name: {
      [Op.like]: '%张%',           // 包含"张"
      [Op.notLike]: '%admin%',     // 不包含"admin"
      [Op.iLike]: '%ZHANG%',       // 忽略大小写包含（PostgreSQL）
      [Op.notILike]: '%ADMIN%',    // 忽略大小写不包含（PostgreSQL）
      [Op.startsWith]: '张',       // 以"张"开头
      [Op.endsWith]: '三',         // 以"三"结尾
      [Op.substring]: '张三'       // 包含"张三"
    }
  }
});

// 正则表达式（MySQL/PostgreSQL）
const users = await User.findAll({
  where: {
    email: {
      [Op.regexp]: '^[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z]{2,}$'
    }
  }
});

// 全文搜索（MySQL）
const articles = await Article.findAll({
  where: {
    [Op.and]: [
      sequelize.literal("MATCH(title, content) AGAINST('关键词' IN NATURAL LANGUAGE MODE)")
    ]
  }
});

2. 数组操作符 ​

javascript
// 数组包含（PostgreSQL）
const users = await User.findAll({
  where: {
    tags: {
      [Op.contains]: ['javascript', 'node.js']  // 包含这些标签
    }
  }
});

// 数组重叠
const users = await User.findAll({
  where: {
    skills: {
      [Op.overlap]: ['react', 'vue', 'angular']  // 至少包含其中一个
    }
  }
});

// 数组长度
const users = await User.findAll({
  where: sequelize.where(
    sequelize.fn('array_length', sequelize.col('tags'), 1),
    Op.gt,
    3
  )
});

3. JSON 操作符 ​

javascript
// JSON 字段查询（MySQL 5.7+, PostgreSQL）
const products = await Product.findAll({
  where: {
    'metadata.color': 'red',                    // JSON 路径查询
    'metadata.size': { [Op.in]: ['M', 'L'] }   // JSON 字段条件
  }
});

// PostgreSQL JSON 操作符
const products = await Product.findAll({
  where: {
    metadata: {
      [Op.contains]: { brand: 'Nike' }          // JSON 包含
    }
  }
});

// MySQL JSON 函数
const products = await Product.findAll({
  where: sequelize.where(
    sequelize.fn('JSON_EXTRACT', sequelize.col('metadata'), '$.price'),
    Op.gt,
    100
  )
});

4. 地理位置操作符 ​

javascript
// 地理位置查询（支持 PostGIS）
const locations = await Location.findAll({
  where: {
    coordinates: {
      [Op.within]: sequelize.fn('ST_GeomFromText', 'POLYGON((...))')
    }
  }
});

// 距离查询
const nearbyLocations = await Location.findAll({
  where: sequelize.where(
    sequelize.fn('ST_Distance', 
      sequelize.col('coordinates'),
      sequelize.fn('ST_GeomFromText', 'POINT(116.404 39.915)')
    ),
    Op.lt,
    1000  // 1000米内
  )
});

子查询和关联查询 ​

1. 子查询 ​

javascript
// EXISTS 子查询
const usersWithPosts = await User.findAll({
  where: {
    [Op.and]: [
      sequelize.literal(`
        EXISTS (
          SELECT 1 FROM posts 
          WHERE posts.user_id = User.id 
          AND posts.status = 'published'
        )
      `)
    ]
  }
});

// IN 子查询
const activeUsers = await User.findAll({
  where: {
    id: {
      [Op.in]: sequelize.literal(`
        (SELECT DISTINCT user_id FROM posts WHERE created_at > '2024-01-01')
      `)
    }
  }
});

// 相关子查询
const usersWithLatestPost = await User.findAll({
  attributes: [
    'id',
    'name',
    [
      sequelize.literal(`(
        SELECT title FROM posts 
        WHERE posts.user_id = User.id 
        ORDER BY created_at DESC 
        LIMIT 1
      )`),
      'latestPostTitle'
    ]
  ]
});

2. 窗口函数（PostgreSQL/MySQL 8.0+） ​

javascript
// 排名查询
const rankedUsers = await User.findAll({
  attributes: [
    'id',
    'name',
    'score',
    [
      sequelize.literal('ROW_NUMBER() OVER (ORDER BY score DESC)'),
      'rank'
    ]
  ],
  raw: true
});

// 分组排名
const topUsersByCategory = await User.findAll({
  attributes: [
    'id',
    'name',
    'category',
    'score',
    [
      sequelize.literal('ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC)'),
      'categoryRank'
    ]
  ],
  where: sequelize.literal('ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) <= 3'),
  raw: true
});

复杂条件组合 ​

1. 动态查询条件 ​

javascript
async function searchUsers(filters) {
  const whereClause = {};
  
  // 动态添加条件
  if (filters.keyword) {
    whereClause[Op.or] = [
      { name: { [Op.like]: `%${filters.keyword}%` } },
      { email: { [Op.like]: `%${filters.keyword}%` } }
    ];
  }
  
  if (filters.status && filters.status.length > 0) {
    whereClause.status = { [Op.in]: filters.status };
  }
  
  if (filters.ageRange) {
    whereClause.age = {
      [Op.between]: [filters.ageRange.min, filters.ageRange.max]
    };
  }
  
  if (filters.dateRange) {
    whereClause.createdAt = {
      [Op.between]: [filters.dateRange.start, filters.dateRange.end]
    };
  }
  
  // 高级筛选
  if (filters.hasAvatar !== undefined) {
    whereClause.avatar = filters.hasAvatar 
      ? { [Op.not]: null }
      : { [Op.is]: null };
  }
  
  return await User.findAll({
    where: whereClause,
    order: [['createdAt', 'DESC']]
  });
}

2. 条件构建器 ​

javascript
class QueryBuilder {
  constructor(model) {
    this.model = model;
    this.whereClause = {};
    this.includeClause = [];
    this.orderClause = [];
    this.limitValue = null;
    this.offsetValue = null;
  }
  
  where(field, operator, value) {
    if (value !== undefined && value !== null && value !== '') {
      this.whereClause[field] = { [operator]: value };
    }
    return this;
  }
  
  orWhere(conditions) {
    if (!this.whereClause[Op.or]) {
      this.whereClause[Op.or] = [];
    }
    this.whereClause[Op.or].push(conditions);
    return this;
  }
  
  like(field, value) {
    if (value) {
      this.whereClause[field] = { [Op.like]: `%${value}%` };
    }
    return this;
  }
  
  in(field, values) {
    if (values && values.length > 0) {
      this.whereClause[field] = { [Op.in]: values };
    }
    return this;
  }
  
  between(field, start, end) {
    if (start && end) {
      this.whereClause[field] = { [Op.between]: [start, end] };
    }
    return this;
  }
  
  include(association) {
    this.includeClause.push(association);
    return this;
  }
  
  orderBy(field, direction = 'ASC') {
    this.orderClause.push([field, direction]);
    return this;
  }
  
  limit(limit) {
    this.limitValue = limit;
    return this;
  }
  
  offset(offset) {
    this.offsetValue = offset;
    return this;
  }
  
  async execute() {
    const options = {
      where: this.whereClause
    };
    
    if (this.includeClause.length > 0) {
      options.include = this.includeClause;
    }
    
    if (this.orderClause.length > 0) {
      options.order = this.orderClause;
    }
    
    if (this.limitValue) {
      options.limit = this.limitValue;
    }
    
    if (this.offsetValue) {
      options.offset = this.offsetValue;
    }
    
    return await this.model.findAll(options);
  }
}

// 使用示例
const users = await new QueryBuilder(User)
  .like('name', '张')
  .in('status', ['active', 'pending'])
  .between('age', 18, 65)
  .include('profile')
  .orderBy('createdAt', 'DESC')
  .limit(10)
  .execute();

聚合查询和统计 ​

1. 复杂聚合 ​

javascript
// 多维度统计
const userStats = await User.findAll({
  attributes: [
    'status',
    [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
    [sequelize.fn('AVG', sequelize.col('age')), 'avgAge'],
    [sequelize.fn('MIN', sequelize.col('createdAt')), 'firstRegistration'],
    [sequelize.fn('MAX', sequelize.col('createdAt')), 'lastRegistration']
  ],
  group: ['status'],
  raw: true
});

// 条件聚合
const conditionalStats = await User.findAll({
  attributes: [
    'department',
    [sequelize.fn('COUNT', sequelize.col('id')), 'totalUsers'],
    [
      sequelize.fn('COUNT', 
        sequelize.literal("CASE WHEN status = 'active' THEN 1 END")
      ),
      'activeUsers'
    ],
    [
      sequelize.fn('COUNT',
        sequelize.literal("CASE WHEN age >= 18 THEN 1 END")
      ),
      'adultUsers'
    ]
  ],
  group: ['department'],
  raw: true
});

2. 时间维度统计 ​

javascript
// 按日期分组统计
async function getDailyUserStats(startDate, endDate) {
  return await User.findAll({
    attributes: [
      [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'],
      [sequelize.fn('COUNT', sequelize.col('id')), 'registrations'],
      [
        sequelize.fn('COUNT',
          sequelize.literal("CASE WHEN status = 'active' THEN 1 END")
        ),
        'activeRegistrations'
      ]
    ],
    where: {
      createdAt: {
        [Op.between]: [startDate, endDate]
      }
    },
    group: [sequelize.fn('DATE', sequelize.col('createdAt'))],
    order: [[sequelize.fn('DATE', sequelize.col('createdAt')), 'ASC']],
    raw: true
  });
}

// 按小时分组统计
async function getHourlyStats() {
  return await User.findAll({
    attributes: [
      [sequelize.fn('HOUR', sequelize.col('createdAt')), 'hour'],
      [sequelize.fn('COUNT', sequelize.col('id')), 'count']
    ],
    where: {
      createdAt: {
        [Op.gte]: sequelize.literal('CURDATE()')  // 今天
      }
    },
    group: [sequelize.fn('HOUR', sequelize.col('createdAt'))],
    order: [[sequelize.fn('HOUR', sequelize.col('createdAt')), 'ASC']],
    raw: true
  });
}

性能优化查询 ​

1. 查询优化技巧 ​

javascript
// 使用索引提示（MySQL）
const users = await User.findAll({
  where: sequelize.literal('USE INDEX (idx_status_created) status = "active"')
});

// 强制索引
const users = await User.findAll({
  where: sequelize.literal('FORCE INDEX (idx_email) email LIKE "%@company.com"')
});

// 查询计划分析
const explain = await sequelize.query('EXPLAIN SELECT * FROM users WHERE status = "active"');
console.log('查询计划:', explain);

2. 分页优化 ​

javascript
// 传统分页（大偏移量性能差）
async function getTraditionalPagination(page, limit) {
  const offset = (page - 1) * limit;
  return await User.findAll({
    limit,
    offset,
    order: [['id', 'ASC']]
  });
}

// 游标分页（性能更好）
async function getCursorPagination(lastId = 0, limit = 10) {
  return await User.findAll({
    where: {
      id: { [Op.gt]: lastId }
    },
    limit,
    order: [['id', 'ASC']]
  });
}

// 混合分页策略
async function getOptimizedPagination(page, limit, useTraditional = false) {
  if (useTraditional || page <= 10) {
    // 前几页使用传统分页
    return getTraditionalPagination(page, limit);
  } else {
    // 后面的页使用游标分页
    const skipCount = (page - 1) * limit;
    const lastUser = await User.findOne({
      offset: skipCount - 1,
      order: [['id', 'ASC']],
      attributes: ['id']
    });
    
    return getCursorPagination(lastUser ? lastUser.id : 0, limit);
  }
}

3. 缓存查询 ​

javascript
const Redis = require('redis');
const redis = Redis.createClient();

async function getCachedUsers(cacheKey, queryOptions) {
  // 尝试从缓存获取
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }
  
  // 从数据库查询
  const users = await User.findAll(queryOptions);
  
  // 存入缓存（5分钟过期）
  await redis.setex(cacheKey, 300, JSON.stringify(users));
  
  return users;
}

// 使用示例
const activeUsers = await getCachedUsers(
  'users:active',
  { where: { status: 'active' } }
);

实战案例 ​

1. 电商商品搜索 ​

javascript
async function searchProducts(params) {
  const {
    keyword,
    categoryId,
    priceRange,
    brand,
    rating,
    inStock,
    sortBy = 'relevance',
    page = 1,
    limit = 20
  } = params;
  
  const whereClause = {};
  const orderClause = [];
  
  // 关键词搜索
  if (keyword) {
    whereClause[Op.or] = [
      { name: { [Op.like]: `%${keyword}%` } },
      { description: { [Op.like]: `%${keyword}%` } },
      { tags: { [Op.like]: `%${keyword}%` } }
    ];
  }
  
  // 分类筛选
  if (categoryId) {
    whereClause.categoryId = categoryId;
  }
  
  // 价格范围
  if (priceRange) {
    whereClause.price = {
      [Op.between]: [priceRange.min, priceRange.max]
    };
  }
  
  // 品牌筛选
  if (brand && brand.length > 0) {
    whereClause.brand = { [Op.in]: brand };
  }
  
  // 评分筛选
  if (rating) {
    whereClause.rating = { [Op.gte]: rating };
  }
  
  // 库存筛选
  if (inStock) {
    whereClause.stock = { [Op.gt]: 0 };
  }
  
  // 排序
  switch (sortBy) {
    case 'price_asc':
      orderClause.push(['price', 'ASC']);
      break;
    case 'price_desc':
      orderClause.push(['price', 'DESC']);
      break;
    case 'rating':
      orderClause.push(['rating', 'DESC']);
      break;
    case 'newest':
      orderClause.push(['createdAt', 'DESC']);
      break;
    case 'sales':
      orderClause.push(['salesCount', 'DESC']);
      break;
    default:
      // 相关性排序（简化版）
      if (keyword) {
        orderClause.push([
          sequelize.literal(`
            CASE 
              WHEN name LIKE '%${keyword}%' THEN 1
              WHEN description LIKE '%${keyword}%' THEN 2
              ELSE 3
            END
          `),
          'ASC'
        ]);
      }
      orderClause.push(['salesCount', 'DESC']);
  }
  
  const { count, rows } = await Product.findAndCountAll({
    where: whereClause,
    order: orderClause,
    limit,
    offset: (page - 1) * limit,
    include: [
      {
        model: Category,
        attributes: ['id', 'name']
      }
    ]
  });
  
  return {
    products: rows,
    total: count,
    page,
    totalPages: Math.ceil(count / limit)
  };
}

2. 用户行为分析 ​

javascript
async function getUserBehaviorAnalysis(userId, days = 30) {
  const startDate = new Date(Date.now() - days * 24 * 60 * 60 * 1000);
  
  // 活动统计
  const activityStats = await UserActivity.findAll({
    attributes: [
      'action',
      [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
      [sequelize.fn('DATE', sequelize.col('createdAt')), 'date']
    ],
    where: {
      userId,
      createdAt: { [Op.gte]: startDate }
    },
    group: ['action', sequelize.fn('DATE', sequelize.col('createdAt'))],
    order: [[sequelize.fn('DATE', sequelize.col('createdAt')), 'ASC']],
    raw: true
  });
  
  // 访问时间分布
  const hourlyDistribution = await UserActivity.findAll({
    attributes: [
      [sequelize.fn('HOUR', sequelize.col('createdAt')), 'hour'],
      [sequelize.fn('COUNT', sequelize.col('id')), 'count']
    ],
    where: {
      userId,
      createdAt: { [Op.gte]: startDate }
    },
    group: [sequelize.fn('HOUR', sequelize.col('createdAt'))],
    order: [[sequelize.fn('HOUR', sequelize.col('createdAt')), 'ASC']],
    raw: true
  });
  
  // 最常访问的页面
  const topPages = await UserActivity.findAll({
    attributes: [
      'page',
      [sequelize.fn('COUNT', sequelize.col('id')), 'visits']
    ],
    where: {
      userId,
      action: 'page_view',
      createdAt: { [Op.gte]: startDate }
    },
    group: ['page'],
    order: [[sequelize.fn('COUNT', sequelize.col('id')), 'DESC']],
    limit: 10,
    raw: true
  });
  
  return {
    activityStats,
    hourlyDistribution,
    topPages
  };
}

总结 ​

今天我们深入学习了 Sequelize 的高级查询技巧：

  • ✅ 各种高级操作符的使用方法
  • ✅ 子查询和窗口函数的应用
  • ✅ 复杂条件的动态构建
  • ✅ 聚合查询和统计分析
  • ✅ 性能优化的实用技巧
  • ✅ 实际项目中的复杂查询案例

掌握了这些知识，你就能够：

  • 处理各种复杂的查询需求
  • 构建高性能的查询代码
  • 实现复杂的业务逻辑
  • 进行深入的数据分析

下一篇文章，我们将学习 Sequelize 的关联关系，这是构建复杂应用的重要基础。

相关文章推荐：

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