I have two functionalities working individually but want to combine them.

• Functionality 1 - Sort users by their geoNear distance.
• Functionality 2 - The users should not have already been liked by the current user (look up partnership collection)

How to update this query to start from the user's collection so I can do geoNear?

The output in the below mongoplayground is correct except that the resulting users are not sorted by calculatedDist which is a field calculated by geoNear.

$geoNear: {
   near: { type: "Point", coordinates: [x,y },
   distanceField: "calculatedDist",
   spherical: true
}

geoNear needs location which is only available in users collection hence I think below query needs to be modified to start in user's collection. https://mongoplayground.net/p/7H_NxciKezB

db={
  users: [
    {
      _id: "abc",
      name: "abc",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          54.23,
          67.12
        ]
      },
      calculatedDist: 13
    },
    {
      _id: "xyz",
      name: "xyyy",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          54.23,
          67.12
        ]
      },
      calculatedDist: 11
    },
    {
      _id: "123",
      name: "yyy",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          54.23,
          67.12
        ]
      },
      calculatedDist: 2
    },
    {
      _id: "rrr",
      name: "tttt",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          54.23,
          67.12
        ]
      },
      calculatedDist: 11
    },
    {
      _id: "eee",
      name: "uuu",
      group: 1,
      location: {
        type: "Point",
        coordinates: [
          54.23,
          67.12
        ]
      },
      calculatedDist: 7
    },
    
  ],
  partnership: [
    {
      _id: "abc_123",
      fromUser: "abc",
      toUser: "123"
    },
    {
      _id: "eee_rrr",
      fromUser: "eee",
      toUser: "rrr"
    },
    {
      _id: "rrr_abc",
      fromUser: "rrr",
      toUser: "abc"
    },
    {
      _id: "abc_rrr",
      fromUser: "abc",
      toUser: "rrr"
    },
    {
      _id: "xyz_rrr",
      fromUser: "xyz",
      toUser: "rrr"
    },
    {
      _id: "rrr_eee",
      fromUser: "rrr",
      toUser: "eee"
    },
    
  ]
}

geoNear as far as I know has to be the first thing to be done so my query should start with the users collection. This breaks my partnership check because for that to work, I start at partnership collection.

In the playground above, the user eee has a lesser calculated distance as a result of geoNear but it shows after user abc.

CodePudding user response：

Try this out:

db.partnership.aggregate([
  // $geoNear
  {
    $match: {
      $or: [
        {
          fromUser: "rrr"
        },
        {
          toUser: "rrr"
        }
      ]
    }
  },
  {
    $group: {
      _id: 0,
      from: {
        $addToSet: "$fromUser"
      },
      to: {
        $addToSet: "$toUser"
      }
    }
  },
  {
    $project: {
      _id: 0,
      users: {
        $filter: {
          input: {
            $setIntersection: [
              "$from",
              "$to"
            ]
          },
          cond: {
            $ne: [
              "$$this",
              "rrr"
            ]
          }
        }
      }
    }
  },
  {
    $lookup: {
      from: "users",
      let: {
        userId: "$users"
      },
      pipeline: [
        {
          "$geoNear": {
            "near": {
              "type": "Point",
              "coordinates": [
                31.4998,
                -61.4065
              ]
            },
            "distanceField": "calculatedDist",
            "spherical": true
          }
        },
        {
          "$match": {
            "$expr": {
              "$in": [
                "$_id",
                "$$userId"
              ]
            }
          }
        }
      ],
      as: "users"
    }
  },
  {
    $project: {
      users: 1,
      count: {
        $size: "$users"
      }
    }
  }
])

Here, we use the pipelined form of lookup.

1. The lookup is on the user's collection, in which we specify a pipeline with the $geoNear stage as the first stage.
2. And finally filter out and only keep the users belonging to a partnership.

This is the playground link. Let me know if it works, on the playground I can't test it because $geoNear requires a 2d index.

While using calculatedDist, it looks like this:

db.partnership.aggregate([
  // $geoNear
  {
    $match: {
      $or: [
        {
          fromUser: "rrr"
        },
        {
          toUser: "rrr"
        }
      ]
    }
  },
  {
    $group: {
      _id: 0,
      from: {
        $addToSet: "$fromUser"
      },
      to: {
        $addToSet: "$toUser"
      }
    }
  },
  {
    $project: {
      _id: 0,
      users: {
        $filter: {
          input: {
            $setIntersection: [
              "$from",
              "$to"
            ]
          },
          cond: {
            $ne: [
              "$$this",
              "rrr"
            ]
          }
        }
      }
    }
  },
  {
    $lookup: {
      from: "users",
      let: {
        userId: "$users"
      },
      pipeline: [
        {
          $sort: {
            calculatedDist: 1
          }
        },
        {
          "$match": {
            "$expr": {
              "$in": [
                "$_id",
                "$$userId"
              ]
            }
          }
        }
      ],
      as: "users"
    }
  },
  {
    $project: {
      users: 1,
      count: {
        $size: "$users"
      }
    }
  }
])

Playground.