Joining tables in Flask-SqlAlchemy

Question

Suppose I have several tables and want to perform join query:

schedule_calendars = ScheduleCalendar.query\
            .join(Schedule)\
            .join(ClinicBranchHasDoctor)\
            .filter_by(clinic_branch_id=clinic_id, doctor_has_specialty_id=doctor_speciality_id).all()

The thing is here is that my result will only contain attributes of ScheduleCalendar class. How do I query such that my result will contain attributes of all joined tables.

Schedule:

    id = Column(db.Integer, primary_key=True)
    added_date = Column(db.DateTime(timezone=True), default=get_current_time, nullable=False)
    start_date = Column(db.Date, nullable=False)
    name = Column(db.String(128), nullable=False)
    is_valid = Column(db.Boolean, default=IS_VALID, nullable=False)
    slot_size = Column(db.Integer, default=30)

ScheduleCalendar:

  schedule_id = Column(db.Integer, db.ForeignKey("schedules.id"), nullable=False)

ClientBranchHasDoctor:

schedule_id = Column(db.Integer, db.ForeignKey("schedules.id"), nullable=False)

I skipped some attributes here. I think the most important is that my tables have appropriate constraints, otherwise join will fail.

Answer 1

You need to add a back reference to your classes.

For example, in your ScheduleCalendar class, add:

schedule_id = Column(db.Integer, db.ForeignKey("schedules.id"), nullable=False)
schedule = db.relationship("Schedule", back_populates="calendar", lazy="dynamic")

And in your Schedule class add:

calendar = db.relationship("ScheduleCalendar", back_populates="schedule")

Now you can access Schedule objects from ScheduleCalendar.

In your example, you would access it like this:

schedule_calendars = ScheduleCalendar.query\
            .join(Schedule)\
            .join(ClinicBranchHasDoctor)\
            .filter_by(clinic_branch_id=clinic_id, doctor_has_specialty_id=doctor_speciality_id).all()

schedule_calendars[0].schedule

Answer 2

I tried many answers but was not able to join tables to get its column data at the same time. After creating back reference as suggested by @AArias you can use this code to get your table's data.

results = db.session.query(Schedule, ScheduleCalendar, ClientBranchHasDoctor). \
      select_from(Schedule).join(ScheduleCalendar).join(ClientBranchHasDoctor).all()

for schedule,scheduleCalendar,hasDoctor in results:
     print(schedule.name, scheduleCalendar.schedule_id , hasDoctor.schedule_id)

This way you can access all data of 3 tables simultaneously.