0

The SQL Query I would like to get into SQLAlchemy form. But I couldn't write the query with sqlalchemy framework.

Query:

SELECT * FROM Availability_Schedule 
WHERE Schedule_ID NOT IN (Select Schedule_ID FROM Appointments WHERE Status = 1) 
AND Staff_ID = 1

SQL Models:

class AvailabilitySchedule(db.Model):
__tablename__ = 'Availability_Schedule'

Schedule_ID = Column(INTEGER, primary_key=True)
Schedule_Date = Column(Date)
Slot_ID = Column(ForeignKey('Time_Slots.Slot_ID', ondelete='RESTRICT'), nullable=False, index=True)
Staff_ID = Column(ForeignKey('Hospital_Staff.Staff_ID'), nullable=False, index=True)
Room_ID = Column(ForeignKey('Rooms.Room_ID'), index=True)

Room = relationship('Room')
Time_Slot = relationship('TimeSlot')
Hospital_Staff = relationship('HospitalStaff')

class Appointment(db.Model):
__tablename__ = 'Appointments'

Appointment_ID = Column(INTEGER, primary_key=True, autoincrement=True)
Schedule_ID = Column(ForeignKey('Availability_Schedule.Schedule_ID'), index=True)
Status = Column(Integer)
Patient_ID = Column(ForeignKey('Patients.Patient_ID'), index=True)
Type = Column(String(255))

Patient = relationship('Patient')
Availability_Schedule = relationship('AvailabilitySchedule')

SQL Alchemy:

schedules = AvailabilitySchedule.query.filter_by(
                                Staff_ID = doctorID,
                                Schedule_ID,not_in(Appointment.query.filter_by(Status = 1).all())
                                                         ).all()
Ugurcan Kaya
  • 137
  • 1
  • 8
  • See https://stackoverflow.com/questions/37792261/sqlalchemy-in-subquery and https://stackoverflow.com/questions/6206600/sqlalchemy-subquery-in-a-where-clause – Ilja Everilä Dec 10 '22 at 19:38

1 Answers1

0

Here's the two tricks you need.

  1. Sqlalchemy offers this function: .in_( ... )
  2. Negation is handled with this operator: ~
J_H
  • 17,926
  • 4
  • 24
  • 44