CODEWITHSUNDEEP
pythonpython-3.xsqlalchemyflask-sqlalchemy
Shraddha
Shraddha

Reputation: 174

SqlAlchemy : I want to filter query on 2 foreign keys

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?

Upvotes: 2

Views: 1547

Answers (2)

AzamAbbasi
AzamAbbasi

Reputation: 101

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()

Upvotes: 0

Martijn Pieters
Martijn Pieters

Reputation: 1121416

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.

Upvotes: 2

Related Questions