How to select data from one collection to another?

Question

MongoDB

there is an endpoint /search, to which data comes from filters from one, or from two and more, or does not come at all if the filters are not set, in accordance with the received data, we form a query object to query the database.

app.post('/search', function(req, res) {
        //все жилье по умолчанию (занятое и свободное)
        var query = {}
        var filter = {}

        //price
        if (req.body.min && req.body.max) {      
            query.cost_per_night = {$gte: req.body.min, $lte: req.body.max}
            //по возрастанию
            filter.cost_per_night = 1
        }
        //dates
        if (req.body.from && req.body.to) {
            query.reserved = { 
            //все свободное жилье
                
                $not: {
                    $elemMatch: {from: {$lt: new Date(req.body.to)}, to: {$gt: new Date(req.body.from)}}
                }
            }
        }
        if (req.body.guests > 1) {
            query.occupancy = {$gte: req.body.guests}
        }
        if (req.body.search) {
            query.city = { $regex: req.body.search.toLowerCase() }
        }
        Nomer
            .find(query)
            .sort(filter)
            .then(function (err, result) {
                if(err){
                    res.send(err);
                } else {
                    res.json(result);
                }
            })
    })

Previously, everything lay in one scheme and there were no problems with the selection. But now I have made two schemes Reservation and Nomer.

const reservationSchema = mongoose.Schema({
  nomer: { type: ObjectId, ref: 'Nomer' },
  from: { type: Date },
  to: { type: Date },
}

const nomerSchema = mongoose.Schema({
  type: String,
  beds: Number,
  reservations: [{ type: ObjectId, ref: 'Reservation' }],
  occupancy: {
      type: Number,
      default: 1,
  },
  owner: {
    type: ObjectId,
    ref: 'User'
  },
  cost_per_night: Number

Проблема: если пришли данные from и to, как сделать выборку всех незанятых номеров как и раньше и при этом, если данные от других фильтров пришли, чтобы все вместе работало?

Answer 1

Look towards the Aggregation Framework, in particular the $lookup operator. But it is rather weak, so it is quite possible that you will have to make two queries to the database and process the data manually in the code.

Answer 2

I would do a simple query by index (from-to) - get all busy for a period. And already on the backend (from cache or mongo) I would give out everything that was not included in the list (because there are not many numbers).
This is one of the most productive options. And complex queries and queries with negation (not) often lead to exhaustive search.
PS: but for an educational project, it’s not a fact that it’s suitable.