0

So I'm having the issue where I need to filter my documents based on the result of a .populate. I can't simply do it in a match parameter in the populate() options because I want to check the size of the resulting array from populate.

Example:

Hotel model:

var HotelSchema = new Schema({
  name: {
    type: String,
    required: true,
  }
});

HotelSchema.virtual('bookings', {
  ref: 'Booking',
  localField: '_id',
  foreignField: 'hotelId',
  justOne: false,
});

Booking model:

var BookingSchema = new Schema({
  startDate: {
    type: Date,
    required: true,
  }
  endDate: {
    type: Date,
    required: true,
  }
  hotelId: {
    type: ObjectId,
    required: true,
  }
});

So in this scenario I would want to do a query on hotels that have no bookings (actually want to do with availability, but simplified it for the example). I can do this by joining to bookings (filtered based on dates), and check if any bookings exist.

I managed to do this with a $lookup like so:

Hotel.aggregate([
  { $lookup: { from: 'bookings', localField: '_id', foreignField: 'hotelId', as: 'bookings' } },
  { $match: { bookings: { $size: 0 }}}
]);

(as a side note, is there a way to only lookup the size of bookings, rather then returning each object? I couldn't seem to figure that out)

But when I do a .populate I can't seem to find any where to add this condition. Something like this is no good:

Hotel.find({})
  .populate({ path: 'bookings', match: { bookings: { $size: 0 } } })

The reason I want to use populate, is because of performance issues as explained here: https://github.com/Automattic/mongoose/issues/3683, and in the case where multiple databases are used, as $lookup would not work in that situation

It appears due to the layout of this relationship, a $lookup would take far longer than a populate, because a full scan of bookings would be done for every Hotel.

Matthew Weeks
  • 920
  • 9
  • 30
  • Hi, it is a little difficult to understand your question. Do you only want to know size of bookings without returning booking documents? By the way there is no problem using lookup aggregate. – SuleymanSah Nov 14 '19 at 18:54
  • All I actually need is the size of bookings, that was just one thing I couldn't get. My actual problem is that I need to only return hotels that have bookings (which would be size of the bookings array, or just the count of bookings). I know how to get the count with mongoose, but not in the $lookup. And as I mentioned, there appears to be performance issues with a $lookup, and it can't be used across multiple databases – Matthew Weeks Nov 14 '19 at 19:02
  • @MatthewWeeks : Are we really talking about two databases or two collections ? I can say .populate() can work between two DB's, But $lookup doesn't !! – whoami - fakeFaceTrueSoul Nov 14 '19 at 20:43
  • @srinivasy Yes I am saying two databases. It isn't required right now, but I can see it being a possibility in the future. That is why I said I would rather use `populate` over `lookup` – Matthew Weeks Nov 14 '19 at 20:51
  • @MatthewWeeks : Ok, for now - So it seems to be - you've achieved what you want !! All you need is hotels without a booking, I think you can see it in $lookup but what you wanted is to get a count to be returned in bookings rather than actual documents is that the ask ? – whoami - fakeFaceTrueSoul Nov 14 '19 at 20:57
  • @srinivasy It's not just the count that I wan't, I want the result of the query to only return Hotels that do not have bookings. So essentially I need the condition `where({ bookingCount: 0 })`, but that doesn't work to filter on the populated field it seems – Matthew Weeks Nov 14 '19 at 21:06
  • @MatthewWeeks : So what I wanted to say is since you're doing aggregate on hotels, I think no matter what you would get all the documents in Hotels collection irrespective of what filter you would be giving in $lookup !! I can certainly post something here if I find anything matching to your requirement, meanwhile how big is your hotel & booking collection ? – whoami - fakeFaceTrueSoul Nov 14 '19 at 22:12
  • @srinivasy There is no production data in it at the moment, so I can't say for sure how large it would be. – Matthew Weeks Nov 15 '19 at 00:26

0 Answers0