CODEWITHSUNDEEP
sequelize.js
RGriffiths
RGriffiths

Reputation: 5970

Sequelize query based on results of another sequelize query

I have a postgres database and I am using Sequelize. From tables vehicles and bookings I am trying to get all vehicles available given a startDate and endDate.

If I search Bookings with the give dates using:

Bookings.findAll({
    where: {
        [Op.or]: [
            { dateOut : { [Op.gt]: req.body.startDate} },
            { dateIn : { [Op.lt]: req.body.startDate} }
        ],
        [Op.or]: [
            { dateOut : { [Op.gt]: req.body.endDate} },
            { dateIn : { [Op.lt]: req.body.endDate} }
        ]
    }
})

I get all the vehicles that are booked during these dates. For exmple a search with startDate 2020-12-12 and endDate 2020-12-13 gives me these bookings:

[
    {
        "id": 13,
        "startDate": "2020-12-05T00:00:00.000Z",
        "endDate": "2020-12-13T00:00:00.000Z",
        "vehicleId": 1
    },
    {
        "id": 12,
        "startDate": "2020-12-12T00:00:00.000Z",
        "endDate": "2020-12-13T00:00:00.000Z",
        "vehicleId": 2
    }
]

Is it possible to use Sequelize to return the vehicles from the Vehicles table that are not found in this initial search?

UPDATE

Following Anatoly's answer I have this. It works in response to the results of the first query:

.then(bookingData => {
    const bookedVehicleIds = bookingData.map(x => x.vehicleId)

    Vehicles.findAll({
        where: {
            id: {
                [Op.notIn]: bookedVehicleIds
            }
        }
    })
    .then(results => {
        res.send(results);
    })

})

Upvotes: 0

Views: 644

Answers (1)

Anatoly
Anatoly

Reputation: 22758

I see no simple way to do one query instead of two ones (only using NOT EXISTS subquery via sequelize.where and sequelize.literal). So if you already have Booking model instances you can just use Op.notIn to get all Vehicles that have not been booked on a given period:

const bookings = await Bookings.findAll({
 // ... where condition omitted
})
const bookedVehicleIds = bookings.map(x => x.vehicleId)
const notBookedVehicles = await Vehicles.findAll({
  where: {
    id: {
      [Op.notIn]: bookedVehicleIds
    }
  }
})

Upvotes: 1

Related Questions