Mongoose按ID统计分销套餐数据：解决查询重复执行错误及金额统计

Node.js项目中提供多种订阅套餐，新用户通过老用户链接购买时，老用户可获福利，相关数据存在affiliation表。需求是给老用户展示其推荐用户中，各套餐的购买人数及对应总金额。

现有代码使用Mongoose的distinct时出现错误：Query was already executed before，虽能输出package_id结果，但无法正确关联套餐金额。

尝试的服务层代码：

exports.getTotalReferral = async (user_id, start_date, end_date) => {
  let total_affiliation_amount = 0;
  let total_affiliation_record = 0;

  start_date = dayjs(start_date).utc(true).format();
  end_date = dayjs(end_date).endOf('day').utc(true).format();

  let data = await Affiliation.find(
    {
      affiliated_by: user_id,
      created_at: {
        $gte: start_date,
        $lte: end_date
      }
    },
  ).distinct('package_id', function (err, result) {
    console.log(result);  // this logs the result of the package id
  });

  return { data, total_affiliation_amount, total_affiliation_record };
}

控制器代码：

exports.totalReferral = async (req, res) => {
  try {
    const total_referral = await affiliationService.getTotalReferral(req.data.id, req.body.start_date, req.body.end_date);
    return res.succeed(total_referral, "success");

  } catch (error) {
    return res.status(404).json({ message: error.message });
  };
};

1. 修复Query was already executed before错误 #

错误原因：同时使用await和回调函数处理distinct查询，Mongoose查询对象只能执行一次，await已触发查询执行，回调会再次触发执行，导致重复执行报错。

解决方法：移除回调函数，直接通过await获取distinct结果：

// 替换原查询代码
const packageIds = await Affiliation.find(
  {
    affiliated_by: user_id,
    created_at: { $gte: start_date, $lte: end_date }
  }
).distinct('package_id');
console.log(packageIds); // 直接拿到去重后的package_id数组

2. 实现套餐购买人数+金额统计 #

要同时统计各套餐的购买人数和总金额，使用Mongoose的聚合查询更高效，无需先查distinct再循环统计，一次聚合即可完成：

情况1：Affiliation表直接存储套餐金额 #

直接用$group分组聚合：

exports.getTotalReferral = async (user_id, start_date, end_date) => {
  start_date = dayjs(start_date).utc(true).toDate(); // 转成Date对象更稳妥
  end_date = dayjs(end_date).endOf('day').utc(true).toDate();

  const stats = await Affiliation.aggregate([
    // 过滤推荐人ID和时间范围
    {
      $match: {
        affiliated_by: user_id,
        created_at: { $gte: start_date, $lte: end_date }
      }
    },
    // 按套餐ID分组，统计人数和总金额
    {
      $group: {
        _id: "$package_id", // 分组键：套餐ID
        count: { $sum: 1 }, // 该套餐购买人数
        totalAmount: { $sum: "$amount" } // 该套餐总金额
      }
    },
    // 重命名字段，让返回结果更直观
    {
      $project: {
        package_id: "$_id",
        count: 1,
        totalAmount: 1,
        _id: 0
      }
    }
  ]);

  // 计算所有推荐记录的总人数和总金额
  const total_affiliation_record = stats.reduce((sum, item) => sum + item.count, 0);
  const total_affiliation_amount = stats.reduce((sum, item) => sum + item.totalAmount, 0);

  return { data: stats, total_affiliation_amount, total_affiliation_record };
}

情况2：套餐金额存储在Package表，需要关联查询 #

用$lookup关联Package表获取金额后再聚合：

exports.getTotalReferral = async (user_id, start_date, end_date) => {
  start_date = dayjs(start_date).utc(true).toDate();
  end_date = dayjs(end_date).endOf('day').utc(true).toDate();

  const stats = await Affiliation.aggregate([
    { $match: { affiliated_by: user_id, created_at: { $gte: start_date, $lte: end_date } } },
    // 关联Package表获取套餐信息
    {
      $lookup: {
        from: "packages", // Package表的集合名称
        localField: "package_id",
        foreignField: "_id",
        as: "package"
      }
    },
    // 展开关联的套餐数组（每个关联仅一条数据）
    { $unwind: "$package" },
    // 按套餐ID分组统计
    {
      $group: {
        _id: "$package_id",
        count: { $sum: 1 },
        totalAmount: { $sum: "$package.price" }, // 取Package表中的价格字段
        packageName: { $first: "$package.name" } // 可选：返回套餐名称
      }
    },
    {
      $project: {
        package_id: "$_id",
        packageName: 1,
        count: 1,
        totalAmount: 1,
        _id: 0
      }
    }
  ]);

  const total_affiliation_record = stats.reduce((sum, item) => sum + item.count, 0);
  const total_affiliation_amount = stats.reduce((sum, item) => sum + item.totalAmount, 0);

  return { data: stats, total_affiliation_amount, total_affiliation_record };
}

3. 返回结果示例 #

控制器调用后返回的结果格式如下：

{
  "data": [
    {
      "package_id": "60d21b4667d0d8992e610c85",
      "count": 3,
      "totalAmount": 30
    },
    {
      "package_id": "60d21b8967d0d8992e610c86",
      "count": 1,
      "totalAmount": 0
    }
  ],
  "total_affiliation_amount": 30,
  "total_affiliation_record": 4
}

内容的提问来源于stack exchange，提问作者John Oliver