Title: Does the order of slqalchemy commands have an effect on the resulting sql query?
Tags: python;sqlalchemy
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 &gt; 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 &gt; 10,
Student.class_id == Class.id)
And is this
result = db.session.query(Class, Student, SpecialNeed) \
.filter(Student.age &gt; 10) \
.filter(Student.class_id == Class.id)
the same as this?
result = db.session.query(Class, Student, SpecialNeed) \
.filter(Student.age &gt; 10,
Student.class_id == Class.id)
Comment: 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

Here is the accepted answer: 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.


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2020:00:05/19/20', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<python><sqlalchemy>', 'Title': 'Does the order of slqalchemy commands have an effect on the resulting sql query?', 'ViewCount': 50, 'id': 61895284, 'user_AboutMe': '<p>Full stack developer and electrical engineer, working in the software development field since 2013.</p>\n', 'user_AccountId': 11525445.0, 'user_DisplayName': 'Nanna', 'user_Reputation': 463.0, 'user_Views': 166.0, 'user_id': 8446061.0}}