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

Question

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

Answer 1

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"
        }
      }
  }
]);