0

I have the classes Shift and Staff that are related in three ways. A Shift object can have a staff1, staff2, and a list of standby staff that is managed with a secondary table. Here are the models.

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 be able to query for all the shifts that a staff member is associated with, either as staff1, staff2, or on standby. I wrote the following SQLAlchemy query:

shifts = Shift.query.filter((Shift.staff1 == staff) |
                            (Shift.staff2 == staff) |
                            (Shift.standby.contains(staff))).all()

The SQL query produced 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, standby_staff AS standby_staff_1
WHERE ? = shifts.staff1_id OR ? = shifts.staff2_id OR shifts.shift_id = standby_staff_1.shift_id AND ? = standby_staff_1.staff_id

Here's the problem. If the staff member is not present in the secondary table (never scheduled as standby staff), then the query returns zero results. As soon as the staff member is in the secondary table, all the expected results are returned.

What's going on here? Shouldn't the fact that the conditions are being linked with OR not necessitate the staff member being in standby? What am I doing wrong?

---EDIT---

To be sure this problem wasn't being caused by logical operators being evaluated in an unexpected order, I added self_group() to the end of the SQLAlchemy query so that the SQL query would contain parentheses around the last two conditions:

def query(staff_id):
    staff = Staff.query.get(staff_id)
    shifts = Shift.query.filter((Shift.staff1 == staff) |
                                (Shift.staff2 == staff) |
                                (Shift.standby.contains(staff)).self_group())

SELECT shifts.shift_id AS shifts_shift_id, shifts.staff1_id AS shifts_staff1_id, shifts.staff2_id AS shifts_staff2_id 
FROM shifts, standby_staff AS standby_staff_1 
WHERE ? = shifts.staff1_id OR ? = shifts.staff2_id OR (shifts.shift_id = standby_staff_1.shift_id AND ? = standby_staff_1.staff_id)
schwartz721
  • 767
  • 7
  • 19
  • https://stackoverflow.com/a/1241158/2144390 – Gord Thompson Feb 07 '21 at 12:29
  • @GordThompson can you explain what you think the problem is? Since AND has precedence over OR, the conditions `shifts.shift_id = standby_staff_1.shift_id AND ? = standby_staff_1.staff_id` should be evaluated first, but then linked to the other conditions with OR. In that case it seems strange that the final condition is affecting the results that should match the first or second condition. What am I missing here? – schwartz721 Feb 07 '21 at 15:41
  • 1
    I suspect that when the query optimizer sees `FROM shifts, standby_staff AS standby_staff_1` followed by `WHERE … shifts.shift_id = standby_staff_1.shift_id` it assumes that you want to do an INNER JOIN between the two tables. I think you really should be doing something like `FROM shifts LEFT JOIN standby_staff ON shifts.shift_id = standby_staff.shift_id`. Perhaps [joined eager loading](https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#joined-eager-loading) might help. – Gord Thompson Feb 07 '21 at 16:01
  • @GordThompson I edited the SQL query and it worked! But now I have to figure out how to write it in SQLAlchemy. The working query 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 tried using `joinedload()` but it didn't produce the right SQL code. – schwartz721 Feb 07 '21 at 16:27

0 Answers0