javascript - Mongoose - 分组、计数并在集合中找不到任何内容时返回 0

Question

我的 mongoDB 数据库中有 2 个集合 - users 和 connections。每个 connection 都有字段 user，其中包含存储在 users 集合中的 user 的 _id .

我需要找到每个用户的连接总数，如果用户没有连接，它应该显示 0 作为他的总数。

同时查找每个用户的 date 字段不等于 '' 的连接数。

我写了这段代码，它返回每个用户的连接总数，但只针对至少有一个连接的用户，没有连接的用户根本不会出现......

Connection.aggregate(
                [
                    {
                        $group: {
                            _id: "$user",
                            total: { $sum: 1 },
                            withDate: { $sum: { $cond: { if: { $ne: ["$date", ""] }, then: 1, else: 0 } } }
                        }
                    },
                    {
                        $lookup: {
                            from: "users",
                            localField: "_id",
                            foreignField: "_id",
                            as: "users"
                        }
                    },
                    {
                        $unwind: "$users"
                    },
                    {
                        $project: {
                            Email: "$users.local.email",
                            "Last Edit": { $dateToString: { format: "%Y-%m-%d", date: "$users.local.lastConnectionEdit" } },
                            "Total Connections": "$total",
                            "Connections With Date": "$withDate"
                        }
                    }
                ],
                function(err, dashboardData) {
                    if (err) {
                        console.log(err);
                        res.status(500).end({ error: "Error fetching stats from database" });
                    } else {
                        let csv = json2csv(dashboardData);

                        res.attachment("stats.csv");
                        res.status(200).send(csv);
                    }
                }
            );

连接架构:

    active:Boolean,
    info: {
        name: String,
        profileImg: String,
        bio: String
    },
    connURL:String,
    handle:String,
    notes: String,
    date: String,
    value: {
        type: Number,
        min: 0,
        max: 5
    },
    tags: [String],
    user: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'User'
    }

用户模式:

local: {
        email: String,
        password: String,
        admin: Boolean,
        snoozeAmount: Number,
        snoozeInterval: String,
        emailIntervalWeeks: Number,
        emailIntervalDay: Number,
        isVerified: { type: Boolean, default: false },
        verifyToken: String,
        passwordResetToken: String,
        lastConnectionEdit: Date
    }

Answer 1

你可以试试下面的聚合

User.aggregate([
  { "$lookup": {
    "from": "connections",
    "localField": "_id",
    "foreignField": "user",
    "as": "connections"
  }},
  { "$addFields": {
    "total": { "$size": "$connections" },
    "withDate": {
      "$reduce": {
        "input": "$connections",
        "initialValue": 0,
        "in": {
          "$add": [
            "$$value",
            { "$sum": {
              "$cond": { "if": { "$ne": [ "$$this.date", "" ] }, "then": 1, "else": 0 }
            }}
          ]
        }
      }
    }
  }}
])