mongoose : Users who have not logged in since last 5 days-CodePudding

I have 2 schemas

userSchema:

 const userSchema = new mongoose.Schema({
  firstName: {
    type: String,
  },
  lastName: {
    type: String,
  },
  username: {
    type: String,
    required: true,
  },
  password: {
    type: String,
    required: true,
  },
  tokens: {
    type: [
      {
        token: String,
      },
    ],
  },
});

useractivity:

const userActivitySchema = new mongoose.Schema({
  ip: String,
  ua: String,
  date: {
    type: Date,
    default: Date.now,
  },
  user: {
    type: mongoose.Schema.Types.ObjectId,
    ref: "User",
  },
});

I am adding a new entry in useractivity every time user logged into the system.

I wanted to featch list of users who haven't logIn in last 5 days for that I write query :

const dateToCheck = new Date();
    dateToCheck.setDate(
      dateToCheck.getDate() - process.env.LEASS_ACTIVE_USER_DAYS
    );
    
    const userActivity = await UserActivity.aggregate([
      { $match: { date: { $lt: dateToCheck } } },

      {
        $lookup: {
          from: "users",
          localField: "user",
          foreignField: "_id",
          as: "userdata",
        },
      },

      { $unwind: { path: "$userdata" } },
      {
        $project: {
          date: 1,
          "userdata.firstName": 1,
        },
      },
    ]);

with this, I am able to get date and firstName of users who haven't logged in last 5 days.

But the issue with this is as there are multiple records in useractivity, above query returns duplicate users in the result.

I tried using { $group: { _id: { user: "$user" } } } to group the result by foreign key but it returns [] in this case.

Any suggestions will be helpfull

CodePudding user response：

Try this out, you already have access to the unique id of the user from your lookup, so use that to group your documents. Additionally, I am storing all the dates in an array from the grouping

UPDATE: Also attaching mongo playground link where I tried it

const userActivity = await UserActivity.aggregate([
  { $match: { date: { $lt: dateToCheck } } },

  {
    $lookup: {
      from: "users",
      localField: "user",
      foreignField: "_id",
      as: "userdata",
    },
  },

  { $unwind: { path: "$userdata" } },
  {
    $project: {
      date: 1,
      "userdata.firstName": 1,
      "user_id": "$userdata._id"
    },
  },
  {
    $group: {
        "_id": {
          "user_id": "$user_id",
          "user_name": "$userdata.firstName"
        },
        "dates": {
          "$push": "$date"
        }
      }
  }
]);