MongoDB查询无法按字母数字型物料名称排序求助

问题描述 #

在使用Mongoose分页查询Inventory集合时，尝试按关联的Material的name字段排序（物料名称为字母数字混合格式），无论设置升序还是降序，排序都不生效。

实体定义 #

@Schema({ timestamps: true })
export class Inventory {

  @Prop({
    type: MongooseSchema.Types.ObjectId,
    ref: 'Material',
    index: true,
    required: true,
  })
  materialId: MaterialDocument;
}

@Schema({ timestamps: true })
export class Material {
 
  @Prop({ required: true })
  name: string;

  @Prop({ required: true })
  nameAr: string;
}

查询代码（原代码） #

const inventories: any = await this.inventoryModelPag.paginate(
  {
    ...queryToApply,
    supplierId: req.user.supplierId,
    $or: [
      { 'materialId.deletedAt': null },
      { 'materialId.deletedAt': { $exists: false } }
    ]
  },
  {
    sort: paginateOptions.sortBy
      ? {
        [paginateOptions.sortBy]: paginateOptions.sortDirection
          ? paginateOptions.sortDirection
          : -1,
      }
      : DefaultSort,
    lean: true,

    populate: [
      {
        path: 'restaurantId',
        select: {
          name: 1,
          nameAr: 1,
          _id: 1,
        },
      },
      {
        path: 'materialId',
        select: {
          name: 1,
          nameAr: 1,
          _id: 1,
          category: 1,
          uomBase: 1,
          sequenceNumber: 1
        },
        match: { deletedAt: null },
        populate: [{
          path: 'uomBase',
          select: {
            name: 1,
            nameAr: 1,
            measure: 1,
            baseConversionRate: 1,
            _id: 1,
          },
        }, {
          path: 'uomBuy',
          select: {
            name: 1,
            nameAr: 1,
            measure: 1,
            baseConversionRate: 1,
            _id: 1,
          },
        },
        {
          path: 'glMaterialCodeId',
          select: {
            name: 1,
            nameAr: 1,
            _id: 1,
          }
        }],
      },
      {
        path: 'uomBase',
        populate: {
          path: 'baseUnit',
          select: {
            name: 1,
            nameAr: 1,
            measure: 1,
            baseConversionRate: 1,
            _id: 1,
          },
        },
        select: {
          name: 1,
          nameAr: 1,
          _id: 1,
        },
      }

    ],
    ...paginateOptions,
    ...pagination,
  },
);

物料名称示例 #

• Butter 1-Gram
• Mixed Spices-Pack250 Gram
• Oreo Bar
• Demo1-100G pack
• Cake1

分页参数 #

paginateOptions {
  pagination: true,
  sortBy: 'materialId.name',
  sortDirection: 1,
  limit: 20,
  page: 1
}

解决方案 #

问题根源 #

直接使用materialId.name排序时，Mongoose分页插件无法在数据库层面关联Material集合进行排序，而是在populate后尝试内存排序，加上lean: true的设置，导致排序逻辑失效。另外，字母数字混合字符串的默认字典序排序不符合自然排序预期（如Cake10会排在Cake2前面）。

代码实现 #

改用MongoDB聚合管道实现关联查询、过滤、排序和分页，同时支持字母数字自然排序：

const { page = 1, limit = 20, sortBy = 'material.name', sortDirection = 1 } = paginateOptions;
const skip = (page - 1) * limit;

// 构建排序逻辑：支持自然排序字母数字混合字段
const sortStage = {};
if (sortBy === 'materialId.name') {
  sortStage['materialNameAlpha'] = sortDirection;
  sortStage['materialNameNum'] = sortDirection;
} else {
  sortStage[sortBy] = sortDirection;
}

const inventories = await this.inventoryModel.aggregate([
  // 匹配Inventory的查询条件
  {
    $match: {
      ...queryToApply,
      supplierId: req.user.supplierId
    }
  },
  // 关联Material集合
  {
    $lookup: {
      from: 'materials', // Material集合的名称（注意是复数）
      localField: 'materialId',
      foreignField: '_id',
      as: 'material'
    }
  },
  // 展开关联的Material数组（一对一关联，展开后为单个对象）
  { $unwind: '$material' },
  // 过滤已删除的Material
  {
    $match: {
      $or: [
        { 'material.deletedAt': null },
        { 'material.deletedAt': { $exists: false } }
      ]
    }
  },
  // 处理自然排序：提取名称中的字母和数字部分
  {
    $addFields: {
      materialNameAlpha: {
        $trim: {
          input: { $replaceAll: { input: '$material.name', find: /\d+/, replacement: '' } }
        }
      },
      materialNameNum: {
        $toInt: {
          $ifNull: [
            { $getField: { field: 'match', input: { $arrayElemAt: [{ $regexFindAll: { input: '$material.name', regex: /\d+/ } }, 0] } } },
            0
          ]
        }
      }
    }
  },
  // 排序
  { $sort: sortStage },
  // 分页：跳过和限制
  { $skip: skip },
  { $limit: limit },
  // 关联restaurantId集合
  {
    $lookup: {
      from: 'restaurants',
      localField: 'restaurantId',
      foreignField: '_id',
      as: 'restaurantId'
    }
  },
  { $unwind: { path: '$restaurantId', preserveNullAndEmptyArrays: true } },
  // 关联uomBase集合
  {
    $lookup: {
      from: 'uoms', // 替换为uomBase对应的实际集合名称
      localField: 'material.uomBase',
      foreignField: '_id',
      as: 'material.uomBase'
    }
  },
  { $unwind: { path: '$material.uomBase', preserveNullAndEmptyArrays: true } },
  // 按需添加其他关联集合的lookup（如uomBuy、glMaterialCodeId等）
  // ...
  // 选择返回的字段，对应原select逻辑
  {
    $project: {
      // Inventory自身字段
      _id: 1,
      supplierId: 1,
      // Material关联字段
      'material._id': 1,
      'material.name': 1,
      'material.nameAr': 1,
      'material.category': 1,
      'material.uomBase': 1,
      'material.sequenceNumber': 1,
      // restaurantId关联字段
      'restaurantId._id': 1,
      'restaurantId.name': 1,
      'restaurantId.nameAr': 1,
      // 移除临时排序字段
      materialNameAlpha: 0,
      materialNameNum: 0
    }
  }
]);

// 查询总条数，生成分页元数据
const totalResult = await this.inventoryModel.aggregate([
  { $match: { ...queryToApply, supplierId: req.user.supplierId } },
  { $lookup: { from: 'materials', localField: 'materialId', foreignField: '_id', as: 'material' } },
  { $unwind: '$material' },
  { $match: { $or: [{ 'material.deletedAt': null }, { 'material.deletedAt': { $exists: false } }] } },
  { $count: 'total' }
]);

const finalResult = {
  docs: inventories,
  totalDocs: totalResult[0]?.total || 0,
  limit,
  page,
  totalPages: Math.ceil((totalResult[0]?.total || 0) / limit)
};

关键说明 #

1. 使用$lookup在数据库层面关联Material集合，确保排序基于原始数据，避免内存排序的问题。
2. 通过正则拆分+类型转换，实现字母数字混合字符串的自然排序，解决字典序排序的不合理性。
3. 手动实现分页逻辑，并单独查询总计数生成分页元数据，替代原分页插件的功能。
4. 按需添加其他关联集合的$lookup配置，与原代码的populate逻辑保持一致。

内容的提问来源于stack exchange，提问作者BJ Coder