Hi everyone, I am new to MongoDB.
I have a use case for which I have to write a query.
I am working on a project that has Java Spring boot in the backend connected to the MongoDB database.
In a hospital, we have multiple doctors.
Each doctor can have 3 types of appointments.
- Once on a particular date and time slot.
- Every day between 2 distinct dates and on the same time slot.
- Specific days of every week between 2 distinct dates and on the same time slot.
Here are the sample documents for all 3 use cases: https://json-parser.com/83cc64fe.json
We will have a portal to book an appointment.
Now we have a requirement that if a patient tries to book an appointment with a doctor with whom he/she already has a current appointment at the same time/overlapping time frame, then we need to respond to him by telling him he/she already has an existing appointment. Appointments with the same doctor at different time frames shouldn’t be blocked from booking.
I am thinking to write a query something like this: (I have some SQL experience but am a beginner in MongoDB, so not sure how to translate the below query to MongoDB Query)
SELECT * FROM appointments WHERE ( drName="Curr_requested_drNames" AND pName="Curr__requested_pName" AND ("Curr_requested_startDate" <= appointmentSchedule.endDate && "Curr_requested_endDate" >= appointmentSchedule.startDate) AND ("Curr_requested_startTime" <= appointmentSchedule.endTime && "Curr_requested_endTime" >= appointmentSchedule.startTime) AND CONTAINS (appointmentSchedule.days, "Curr_requested_days") );
- In my query I am using multiple AND operations, will that be an anti-pattern query?
- Any thoughts on improving the query. Wanted to make just one DB call to get conflicting appointments.
- Any thoughts on how to translate this SQL-like query to a MongoDB query.