SqlAlchemy : I want to filter query on 2 foreign keys

Question

I have 2 tables (say Student and College) and a third table, which has student_id and college_id foreign keys.

I don't want to hard delete Student and College rows, so I have included a deleted(Boolean) column in both tables.

I want to add a student to a college (by adding student_id and and college_id to the third table). I first want to check if that student already exists in the college and also want to filter the deleted records for student and college.

for the first part I have written:

db.session.query(StudentCollegeMappingModel).filter(
    StudentCollegeMappingModel.student_id==student_id,
    StudentCollegeMappingModel.college_id==college_id
).first()

but I also want to check if

Student.deleted==False and College.deleted==False

How should I do it?

Have you configured relationships between the tables? Can you share a little bit of your model definitions? — Martijn Pieters, Aug 26 '19 at 11:15
@MartijnPieters I see a `session.query` question not a `model` one. Whatsoever — Kostas Charitidis, Aug 26 '19 at 11:23
@KostasCharitidis: that has nothing to do with the question, really. `StudentCollegeMappingModel.query` or `db.session.query(StudentCollegeMappingModel)` are just two ways of creating the initial query object, when using Flask-SQLAlchemy. What has that got to do with your 'use multiple filters' comment? — Martijn Pieters, Aug 26 '19 at 11:26

Martijn Pieters · Accepted Answer · 2019-08-26T11:28:48.180

You'll need to join the Student and College tables to your query so you can filter on the deleted columns:

query = (
    StudentCollegeMappingModel.query
    .join(Student, Student.id == StudentCollegeMappingModel.student_id)
    .join(College, College.id == StudentCollegeMappingModel.college_id)
    .filter(
        StudentCollegeMappingModel.student_id == student_id,
        StudentCollegeMappingModel.college_id == college_id,
        Student.deleted.is_(False),
        College.deleted.is_(False),
    )
)

I used StudentCollegeMappingModel.query rather than db.session.query(StudentCollegeMappingModel); they produce the same initial Query instance, but Flask-SQLAlchemy tries to make it easier for you by giving you the .query property.

You probably can omit the Student.id == StudentCollegeMappingModel.student_id and College.id == StudentCollegeMappingModel.college_id ON clauses here, provided you configured student_id and college_id as ForeignKey() columns and there are no other relationships between the tables that might confuse SQLAlchemy:

query = (
    StudentCollegeMappingModel.query.join(Student).join(College)
    .filter(
        StudentCollegeMappingModel.student_id == student_id,
        StudentCollegeMappingModel.college_id == college_id,
        Student.deleted.is_(False),
        College.deleted.is_(False),
    )
)

You can also use from sqlalchemy.sql.expression.false with ==:

query = (
    StudentCollegeMappingModel.query.join(Student).join(College)
    .filter(
        StudentCollegeMappingModel.student_id == student_id,
        StudentCollegeMappingModel.college_id == college_id,
        Student == false(),
        College == false(),
    )
)

Note that you could use the above query as an NOT EXISTS filter when inserting the new record; that would help prevent race conditions (where multiple pieces of code try to make the same change, or where some are setting deleted columns after you tested this but before you insert.

I've seen aliasing of `True` and `False` to get around using `==` with linters before but wasn't aware of the expression functions. I like that, thanks! — SuperShoot, Aug 26 '19 at 11:27

score 0 · Answer 2 · answered Aug 26 '19 at 11:32

You can use joins

db.session.query(StudentCollegeMappingModel, Student, College)
    .join(StudentCollegeMappingModel)
    .join(Student)
    .join(College)
    .filter(StudentCollegeMappingModel.student_id == Student.id, StudentCollegeMappingModel.college_id==college_id,Student.deleted==False, College.deleted==False)
    ).all()

SqlAlchemy : I want to filter query on 2 foreign keys

2 Answers2