Does the order of slqalchemy commands have an effect on the resulting sql query?

Question

Does the order of commands in my sqlalchemy query have an effect on the order of actions in the resulting sql query?

E.g. is this

result = db.session.query(Class, Student, SpecialNeed) \
                    .filter(Student.age > 10,
                        Student.class_id == Class.id) \
                    .outerjoin(SpecialNeed, \
                        and_(SpecialNeed.student_id == Student.id , \
                            SpecialNeed.valid == True))

the same as this?

result = db.session.query(Class, Student, SpecialNeed) \
                    .outerjoin(SpecialNeed, \
                        and_(SpecialNeed.student_id == Student.id , \
                            SpecialNeed.valid == True))\
                    .filter(Student.age > 10,
                        Student.class_id == Class.id)

And is this

result = db.session.query(Class, Student, SpecialNeed) \
                    .filter(Student.age > 10) \
                    .filter(Student.class_id == Class.id)

the same as this?

result = db.session.query(Class, Student, SpecialNeed) \
                    .filter(Student.age > 10,
                        Student.class_id == Class.id)

Related: https://stackoverflow.com/questions/58276515/any-performance-impact-to-the-order-of-a-compound-sqlalchemy-query, https://stackoverflow.com/questions/43668752/in-sqlalchemy-is-a-filter-applied-before-or-after-a-join — Ilja Everilä, May 19 '20 at 17:16

Ilja Everilä · Accepted Answer · 2020-05-19T17:18:00.093

In your case no, the queries are the same in both cases. The first two are the same due to how SQL works; first the FROM list, then WHERE, and so on. SQL just has strict grammar that dictates the order of clauses compared to the Query builder pattern in SQLAlchemy. In the latter queries filter(x, y) is the same as filter(x).filter(y), both produce x AND y.

There are Query methods in SQLAlchemy that do depend on the order or have an effect on the order of other operations, such as Query.filter_by(), and Query.from_self(). Also there are operations in SQL that depend on the order of operands, such as LEFT JOIN.

So the answer to the title is: it can, but not always. You will have to check the documentation and understand what the operation does in addition to understanding the underlying SQL.

Does the order of slqalchemy commands have an effect on the resulting sql query?

1 Answers1