I have two collections:
users - All the user info
'partnership` - Users can partner/follow each other.
Goal: To get all partnerships sorted by last partnered date.
When do users become partners: Assuming user_1 liked user_2 AND they become partners only when user_2 also liked user_1 (mutual like)
What is partnered date: The date/time when second like (mutual like) happened.
I started a mongo playground but please ignore my query - Mongo Playground
Here is my sample data
db={
partnership: [
{
_id: "xyz_rrr",
updated: "2022-10-23T12:35:24.772 00:00",
users: [
"xyz",
"rrr"
]
},
{
_id: "rrr_eee",
updated: "2022-12-23T12:35:24.772 00:00",
users: [
"rrr",
"eee"
]
},
{
_id: "eee_rrr",
updated: "2023-01-21T12:35:24.772 00:00",
users: [
"eee",
"rrr"
]
},
{
_id: "mmm_rrr",
updated: "2023-02-19T12:35:24.772 00:00",
users: [
"mmm",
"rrr"
]
},
{
_id: "rrr_mmm",
updated: "2023-02-21T12:35:24.772 00:00",
users: [
"rrr",
"mmm"
]
},
],
users: [
{
_id: "abc",
name: "abc",
group: 1,
location: {
type: "Point",
coordinates: [
53.23,
67.12
]
},
calculatedDist: 112
},
{
_id: "xyz",
name: "xyyy",
group: 1,
location: {
type: "Point",
coordinates: [
54.23,
67.12
]
},
calculatedDist: 13
},
{
_id: "123",
name: "yyy",
group: 1,
location: {
type: "Point",
coordinates: [
54.23,
67.12
]
},
calculatedDist: 13
},
{
_id: "rrr",
name: "rrrrrrr",
group: 1,
location: {
type: "Point",
coordinates: [
51.23,
64.12
]
},
calculatedDist: 14
},
{
_id: "mmm",
name: "mmmm",
group: 1,
location: {
type: "Point",
coordinates: [
51.23,
64.12
]
},
calculatedDist: 14
},
{
_id: "eee",
name: "eeeee",
group: 1,
location: {
type: "Point",
coordinates: [
55.23,
62.12
]
},
calculatedDist: 143
}
],
}
Expected result
{
partneredUsers:
{ firstUser :
{
_id: "mmm",
name: "mmmm",
},
},
secondUser :
{
_id: "rrr",
name: "rrrrrrr",
},
},
partneredDate: "2023-02-21T12:35:24.772 00:00",
},
{
partneredUsers:
{ firstUser :
{
_id: "rrr",
name: "rrrrrrr",
},
},
secondUser :
{
_id: "eee",
name: "eeeee",
}
},
partneredDate: "2023-01-23T12:35:24.772 00:00",
}
}
CodePudding user response：
Starting from partnership collection, create a partition key by $sortArray and $concat to identify mutually liked relationship. (They will share the same key). Use the partition key in $setWindowFields to compute count and rank.
- count: pair of users mutually like each other when count > 1
- rank: sort by updated: -1; the latest like will have rank: 1
Finally $lookup from users to get the details of users and $sort by partneredDate.
db.partnership.aggregate([
{
$set: {
partitionKey: {
"$reduce": {
"input": {
$sortArray: {
input: "$users",
sortBy: 1
}
},
"initialValue": "",
"in": {
"$concat": [
"$$value",
"$$this"
]
}
}
}
}
},
{
"$setWindowFields": {
"partitionBy": "$partitionKey",
"sortBy": {
"updated": -1
},
"output": {
"count": {
$sum: 1
},
rank: {
$rank: {}
}
}
}
},
{
$match: {
count: {
$gt: 1
},
rank: 1
}
},
{
"$lookup": {
"from": "users",
"localField": "users",
"foreignField": "_id",
"as": "userLookup"
}
},
{
"$project": {
_id: 0,
partneredUsers: {
firstUser: {
$first: "$userLookup"
},
secondUser: {
$last: "$userLookup"
}
},
partneredDate: "$updated"
}
},
{
$sort: {
partneredDate: -1
}
}
])