I have a mongo collection with activities in this format:
{
"_id": 1,
"user": 1,
"time": 12345,
"data": ...
}
Now I want to get the 5 latest entries (the whole entry) from this collection but I want only one entry for each user in the case there are more than one activities from a user in the latest activities. I do not want to filter the result after the DB query. I hope there is a mongoDB way to do this on the DB server.
I would like to perform this query with Doctrine MongoDB ODM but I suspect that this is not possible with the provided methods. But a direct mongo query is fine too.
You aren't using a date value for your time
, so I'm going to assume "latest" means "largest number in the time
". Secondly, I'm going to get the top 2 latest entries with at most one per user. The idea is that only the highest value of the time
matters for each user, so we just $group
by user
after sorting on time
while projecting the field values from the $first
result seen by $group
, then take the top 2 entries overall. The example is in the mongo shell.
> db.user.find()
{ "_id" : 1, "user" : 1, "time" : 12345, "data" : 48 }
{ "_id" : 2, "user" : 1, "time" : 12346, "data" : 32 }
{ "_id" : 3, "user" : 2, "time" : 347, "data" : 2 }
{ "_id" : 4, "user" : 2, "time" : 384, "data" : 99 }
{ "_id" : 5, "user" : 2, "time" : 384, "data" : 66 }
{ "_id" : 6, "user" : 3, "time" : 3384, "data" : 55 }
{ "_id" : 7, "user" : 3, "time" : 33844, "data" : 3 }
> db.user.aggregate([
{ "$sort" : { "time" : -1 } },
{ "$group" : {
"_id" : "$user",
"time" : { "$first" : "$time" },
"data" : { "$first" : "$data" }
}
},
{ "$sort" : { "time" : -1 } },
{ "$limit" : 2 }
])
{ "_id" : 3, "time" : 33844, "data" : 3 }
{ "_id" : 1, "time" : 12346, "data" : 32 }