API过滤与排序Skill api-filtering-sorting

---

name: api-filtering-sorting description: 实现高级过滤和排序功能的API，包括查询解析、字段验证和优化。用于构建搜索功能、复杂查询或灵活的数据检索端点。

API过滤与排序

概览

构建灵活的过滤和排序系统，高效处理复杂查询，并进行适当的验证、安全和性能优化。

使用场景

• 构建搜索和过滤界面
• 实现高级查询功能
• 创建灵活的数据检索端点
• 优化查询性能
• 验证用户查询输入
• 支持复杂过滤逻辑

指南

1. 查询参数过滤

// Node.js过滤实现
app.get('/api/products', async (req, res) => {
  const filters = {};
  const sortOptions = {};

  // 解析过滤参数
  const allowedFilters = ['category', 'minPrice', 'maxPrice', 'inStock', 'rating'];
  for (const key of allowedFilters) {
    if (req.query[key]) {
      filters[key] = req.query[key];
    }
  }

  // 构建MongoDB查询
  const mongoQuery = {};

  if (filters.category) {
    mongoQuery.category = filters.category;
  }

  if (filters.minPrice || filters.maxPrice) {
    mongoQuery.price = {};
    if (filters.minPrice) {
      mongoQuery.price.$gte = parseFloat(filters.minPrice);
    }
    if (filters.maxPrice) {
      mongoQuery.price.$lte = parseFloat(filters.maxPrice);
    }
  }

  if (filters.inStock !== undefined) {
    mongoQuery.stock = { $gt: filters.inStock === 'true' ? 0 : -1 };
  }

  if (filters.rating) {
    mongoQuery.rating = { $gte: parseFloat(filters.rating) };
  }

  // 解析排序
  const sortField = req.query.sort || 'createdAt';
  const sortOrder = req.query.order === 'asc' ? 1 : -1;

  const validSortFields = ['price', 'rating', 'createdAt', 'popularity'];
  if (!validSortFields.includes(sortField)) {
    return res.status(400).json({ error: '无效的排序字段' });
  }

  const page = parseInt(req.query.page) || 1;
  const limit = Math.min(parseInt(req.query.limit) || 20, 100);
  const offset = (page - 1) * limit;

  try {
    const [products, total] = await Promise.all([
      Product.find(mongoQuery)
        .sort({ [sortField]: sortOrder })
        .skip(offset)
        .limit(limit),
      Product.countDocuments(mongoQuery)
    ]);

    res.json({
      data: products,
      filters: {
        applied: filters,
        available: {
          categories: await getAvailableCategories(),
          priceRange: await getPriceRange(),
          ratings: [1, 2, 3, 4, 5]
        }
      },
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit)
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

2. 高级过滤器解析器

// 解析复杂过滤查询
class FilterParser {
  static parse(queryString) {
    const filters = {};
    const params = new URLSearchParams(queryString);

    params.forEach((value, key) => {
      // 处理嵌套过滤器（例如，user.email, address.city）
      if (key.includes('.')) {
        this.setNested(filters, key, value);
      } else {
        filters[key] = this.parseValue(value);
      }
    });

    return filters;
  }

  static setNested(obj, path, value) {
    const keys = path.split('.');
    let current = obj;

    for (let i = 0; i < keys.length - 1; i++) {
      const key = keys[i];
      if (!current[key]) current[key] = {};
      current = current[key];
    }

    current[keys[keys.length - 1]] = this.parseValue(value);
  }

  static parseValue(value) {
    // 处理操作符语法：gt:100, lt:200, in:a,b,c
    if (typeof value !== 'string') return value;

    const operatorMatch = value.match(/^(eq|ne|gt|gte|lt|lte|in|nin|exists|regex):(.+)$/);
    if (operatorMatch) {
      const [, operator, operandValue] = operatorMatch;

      const operators = {
        eq: { $eq: operandValue },
        ne: { $ne: operandValue },
        gt: { $gt: parseFloat(operandValue) },
        gte: { $gte: parseFloat(operandValue) },
        lt: { $lt: parseFloat(operandValue) },
        lte: { $lte: parseFloat(operandValue) },
        in: { $in: operandValue.split(',') },
        nin: { $nin: operandValue.split(',') },
        exists: { $exists: operandValue === 'true' },
        regex: { $regex: operandValue, $options: 'i' }
      };

      return operators[operator];
    }

    // 解析布尔值
    if (value === 'true') return true;
    if (value === 'false') return false;

    // 解析数字
    if (!isNaN(value)) return parseFloat(value);

    return value;
  }
}

// 使用方法
app.get('/api/advanced-search', async (req, res) => {
  const filters = FilterParser.parse(req.url.split('?')[1]);

  const products = await Product.find(filters);
  res.json({ data: products });
});

// 示例查询：
// /api/advanced-search?price=gte:100&price=lt:500&category=electronics
// /api/advanced-search?rating=gte:4&inStock=exists:true
// /api/advanced-search?tags=in:new,featured&name=regex:laptop

3. 过滤器构建器模式

// 流式过滤器构建器
class QueryBuilder {
  constructor(model) {
    this.model = model;
    this.query = {};
    this.sortBy = {};
    this.pageSize = 20;
    this.pageNum = 1;
  }

  filter(field, operator, value) {
    const operators = {
      '=': '$eq',
      '!=': '$ne',
      '>': '$gt',
      '>=': '$gte',
      '<': '$lt',
      '<=': '$lte',
      'in': '$in',
      'regex': '$regex'
    };

    const mongoOp = operators[operator];
    if (!mongoOp) throw new Error(`无效的操作符：${operator}`);

    this.query[field] = { [mongoOp]: value };
    return this;
  }

  range(field, min, max) {
    this.query[field] = { $gte: min, $lte: max };
    return this;
  }

  search(text, fields) {
    this.query.$or = fields.map(field => ({
      [field]: { $regex: text, $options: 'i' }
    }));
    return this;
  }

  sort(field, direction = 'asc') {
    this.sortBy[field] = direction === 'asc' ? 1 : -1;
    return this;
  }

  pagination(page = 1, limit = 20) {
    this.pageNum = page;
    this.pageSize = Math.min(limit, 100);
    return this;
  }

  async execute() {
    const offset = (this.pageNum - 1) * this.pageSize;

    const [data, total] = await Promise.all([
      this.model.find(this.query)
        .sort(this.sortBy)
        .skip(offset)
        .limit(this.pageSize),
      this.model.countDocuments(this.query)
    ]);

    return {
      data,
      pagination: {
        page: this.pageNum,
        limit: this.pageSize,
        total,
        totalPages: Math.ceil(total / this.pageSize)
      }
    };
  }
}

// 使用方法
const results = await new QueryBuilder(Product)
  .filter('category', '=', 'electronics')
  .range('price', 100, 500)
  .filter('inStock', '=', true)
  .sort('price', 'asc')
  .pagination(1, 20)
  .execute();

4. Python过滤(SQLAlchemy)

from sqlalchemy import and_, or_, func
from sqlalchemy.orm import Query

class FilterSpecification:
    def __init__(self, field, operator, value):
        self.field = field
        self.operator = operator
        self.value = value

    def to_sql(self, model):
        column = getattr(model, self.field)
        operators = {
            'eq': lambda c, v: c == v,
            'ne': lambda c, v: c != v,
            'gt': lambda c, v: c > v,
            'gte': lambda c, v: c >= v,
            'lt': lambda c, v: c < v,
            'lte': lambda c, v: c <= v,
            'in': lambda c, v: c.in_(v),
            'like': lambda c, v: c.ilike(f'%{v}%'),
            'between': lambda c, v: c.between(v[0], v[1])
        }

        operation = operators.get(self.operator)
        if not operation:
            raise ValueError(f'无效的操作符：{self.operator}')

        return operation(column, self.value)

@app.route('/api/products', methods=['GET'])
def list_products():
    category = request.args.get('category')
    min_price = request.args.get('minPrice', type=float)
    max_price = request.args.get('maxPrice', type=float)
    sort_by = request.args.get('sort', 'created_at')
    sort_order = request.args.get('order', 'desc')
    page = request.args.get('page', 1, type=int)
    per_page = min(request.args.get('limit', 20, type=int), 100)

    query = Product.query

    # 应用过滤器
    if category:
        query = query.filter(Product.category == category)

    if min_price:
        query = query.filter(Product.price >= min_price)

    if max_price:
        query = query.filter(Product.price <= max_price)

    # 应用排序
    sort_field = getattr(Product, sort_by, Product.created_at)
    if sort_order == 'asc':
        query = query.order_by(sort_field.asc())
    else:
        query = query.order_by(sort_field.desc())

    # 分页
    pagination = query.paginate(page=page, per_page=per_page)

    return jsonify({
        'data': [p.to_dict() for p in pagination.items],
        'pagination': {
            'page': page,
            'per_page': per_page,
            'total': pagination.total,
            'pages': pagination.pages
        }
    }), 200

5. Elasticsearch过滤

async function searchWithFilters(searchQuery, filters, sort, page = 1, limit = 20) {
  const from = (page - 1) * limit;

  const must = [];
  const should = [];

  // 全文搜索
  if (searchQuery) {
    must.push({
      multi_match: {
        query: searchQuery,
        fields: ['name^2', 'description', 'category']
      }
    });
  }

  // 应用过滤器
  if (filters.category) {
    must.push({ term: { 'category.keyword': filters.category } });
  }

  if (filters.minPrice || filters.maxPrice) {
    const range = {};
    if (filters.minPrice) range.gte = filters.minPrice;
    if (filters.maxPrice) range.lte = filters.maxPrice;
    must.push({ range: { price: range } });
  }

  if (filters.tags) {
    should.push({
      terms: { 'tags.keyword': filters.tags }
    });
  }

  const response = await esClient.search({
    index: 'products',
    body: {
      from,
      size: limit,
      query: {
        bool: {
          must,
          ...(should.length && { should, minimum_should_match: 1 })
        }
      },
      sort: sort ? [sort] : ['_score', { createdAt: 'desc' }],
      aggs: {
        categories: {
          terms: { field: 'category.keyword', size: 50 }
        },
        priceRange: {
          stats: { field: 'price' }
        }
      }
    }
  });

  return {
    results: response.hits.hits.map(hit => hit._source),
    total: response.hits.total.value,
    facets: {
      categories: response.aggregations.categories.buckets,
      priceRange: response.aggregations.priceRange
    }
  };
}

6. 查询验证

// 防止注入和无效查询
const validateFilter = (field, value) => {
  const allowedFields = ['category', 'price', 'rating', 'inStock'];

  if (!allowedFields.includes(field)) {
    throw new Error(`字段 ${field} 不可过滤`);
  }

  // 验证特定字段的值
  const validations = {
    category: (v) => typeof v === 'string' && v.length <= 50,
    price: (v) => !isNaN(v) && v >= 0,
    rating: (v) => !isNaN(v) && v >= 0 && v <= 5,
    inStock: (v) => v === 'true' || v === 'false'
  };

  if (!validations[field](value)) {
    throw new Error(`字段 ${field} 的值无效`);
  }

  return true;
};

最佳实践

✅ 应该

• 白名单允许的过滤字段
• 验证所有输入参数
• 为过滤使用的字段建立索引
• 支持常见操作符
• 提供分类导航
• 缓存过滤选项
• 限制过滤复杂性
• 文档化过滤语法
• 使用数据库原生操作符
• 通过索引优化查询

❌ 不应该

• 允许任意字段过滤
• 支持无限操作符
• 忽视SQL注入风险
• 创建复杂过滤逻辑
• 暴露内部字段名称
• 在未索引字段上过滤
• 允许深层嵌套过滤
• 跳过输入验证
• 将所有过滤器与OR组合
• 忽视性能影响

性能优化

• 为常用过滤器创建复合索引
• 在数据库中使用查询提示
• 缓存频繁的过滤组合
• 限制聚合复杂性
• 监控查询性能
• 使用数据库统计信息
• 考虑非规范化
• 实施查询结果缓存