python - SQLAlchemy join adding unwanted FROM parameter

Question

I have 3 tables in a database that are related: Shift, Staff, and standby_staff (secondary table). Shift is related to Staff through three relationships: staff1, staff2, and standby (a list of standby staff members that is managed by the standby_staff secondary table). Models below:

class Shift(db.Model):
    __tablename__ = 'shifts'
    shift_id = db.Column(db.Integer, primary_key=True)
    staff1_id = db.Column(db.Integer, db.ForeignKey('staff.staff_id'))
    staff1 = db.relationship('Staff', foreign_keys=[staff1_id])
    staff2_id = db.Column(db.Integer, db.ForeignKey('staff.staff_id'))
    staff2 = db.relationship('Staff', foreign_keys=[staff2_id])
    standby = db.relationship('Staff', secondary='standby_staff')

class Staff(db.Model):
    __tablename__ = 'staff'
    staff_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(20))

standby_staff = db.Table('standby_staff', db.metadata,
                         db.Column('shift_id', db.Integer, db.ForeignKey('shifts.shift_id')),
                         db.Column('staff_id', db.Integer, db.ForeignKey('staff.staff_id')))

I want to query for all Shifts that a Staff member is associated with. The SQL query that works is:

SELECT shifts.shift_id AS shifts_shift_id, shifts.staff1_id AS shifts_staff1_id, shifts.staff2_id AS shifts_staff2_id 
FROM shifts LEFT JOIN standby_staff ON shifts.shift_id = standby_staff.shift_id 
WHERE ? = shifts.staff1_id OR ? = shifts.staff2_id OR shifts.shift_id = standby_staff.shift_id AND ? = standby_staff.staff_id

I am trying to reproduce this query in SQLAlchemy and have gotten close, but am having problems with the FROM portion of the query. Here's the SQLAlchemy code and the SQL query it produces:

shifts = Shift.query.join(standby_staff, isouter=True).filter((Shift.staff1 == staff) |
                                                                  (Shift.staff2 == staff) |
                                                                  (Shift.standby.contains(staff)))

SELECT shifts.shift_id AS shifts_shift_id, shifts.staff1_id AS shifts_staff1_id, shifts.staff2_id AS shifts_staff2_id 
FROM standby_staff AS standby_staff_1, shifts LEFT OUTER JOIN standby_staff ON shifts.shift_id = standby_staff.shift_id 
WHERE ? = shifts.staff1_id OR ? = shifts.staff2_id OR shifts.shift_id = standby_staff_1.shift_id AND ? = standby_staff_1.staff_id

As you can see, the only difference between the SQL queries is that the SQLAlchemy query has FROM standby_staff AS standby_staff_1, shifts, where the desired query just has FROM shifts, and this actually leads to different query results. How can I fix the SQLAlchemy query so it produces the desired SQL query?

标签： pythonsqlflasksqlalchemyflask-sqlalchemy