Assume I have a collection called "bookings" storing the following:

{
  vehicle_id: 6307d365fe35112dcd313bdf,
  start_date: 2022-10-11T02:28:30.037Z,
  end_date: 2022-10-12T02:28:30.037Z,
}

And another collection called "vehicles" storing the following:

{
  _id: 6307d365fe35112dcd313bdf,
  model: "Hyundai Tucson"
}

What would be the best way to find all the vehicles that aren't booked in a specific timeframe?

Through Node.js I could do the following:

let startDate = "2022-10-11T02:28:30.037Z"
let endDate = "2022-10-12T02:28:30.037Z"

let vehicles = db.vehicles.find({})
let available = [];
for (let vehicle of vehicles) {
  let bookings = db.bookings.count({ 
    vehicle_id: vehicle._id,  
    start_date: { $gte: startDate },
    end_date: { $lte: endDate }
  })
 
  if (bookings == 0) {
    available.push(vehicle);
  }
}

But I would like to know how I could achieve this same thing through aggregation in order to speed up and reduce database calls.

CodePudding user response：

You can use $lookup with a pipeline:

1. For each vehicle get all bookings with the matching vehicle_id, where there start_date is inside the requested range or their start_date is before the requested range and their `end_date after the requested start.
2. $match only documents which have no such booking.
3. Format

db.vehicles.aggregate([
  {$lookup: {
      from: "bookings",
      let: {vehicle_id: "$_id"},
      pipeline: [
        {$match: {
            $expr: {$and: [
                {$or: [
                    {$and: [
                        {$lt: ["$start_date", endDate]},
                        {$gte: ["$start_date", startDate]}
                    ]},
                    {$and: [
                        {$lte: ["$start_date", startDate]},
                        {$gt: ["$end_date", startDate]}
                    ]}
                ]},
                {$eq: ["$vehicle_id", "$$vehicle_id"]}
            ]}
        }}
      ],
      as: "bookings"
  }},
  {$match: {"bookings.0": {$exists: false}}},
  {$unset: "bookings"}
])

See how it works on the playground example