SQLAlchemy Joinedload filter column

by

The reason it is not working is that joinedload (and all the other relationship loading techniques) are meant to be entirely transparent. That is to say having a joinedload in your query should not affect it in any other way other than resulting in the relationships being filled. You should read “The Zen of Joined Eager Loading”, which begins with:

Since joined eager loading seems to have many resemblances to the use of Query.join(), it often produces confusion as to when and how it should be used. It is critical to understand the distinction that while Query.join() is used to alter the results of a query, joinedload() goes through great lengths to not alter the results of the query, and instead hide the effects of the rendered join to only allow for related objects to be present.

One of the tricks is to use aliases for the joined tables which are not made available. Your query then ends up performing an implicit cross-join between Work and User, and hence the extra rows. So in order to filter against a joined table, use Query.join():

session.query(Work).\
    join(Work.company_users).\
    join(CompanyUser.user).\
    filter(Work.id == 1).\
    filter(User.first_name == 'The name').\
    all()

and if you also need the eagerloads in place, you can instruct the Query that it already contains the joins with contains_eager():

session.query(Work).\
    join(Work.company_users).\
    join(CompanyUser.user).\
    options(contains_eager(Work.company_users).
            contains_eager(CompanyUser.user)).\
    filter(Work.id == 1).\
    filter(User.first_name == 'The name').\
    all()

Note the chained calls to contains_eager().

More Related Contents:

  1. Difference between filter and filter_by in SQLAlchemy
  2. Sqlite / SQLAlchemy: how to enforce Foreign Keys?
  3. SQLAlchemy IN clause
  4. Flask SQLAlchemy query, specify column names
  5. Executing multiple statements with Postgresql via SQLAlchemy does not persist changes
  6. How can I bind a list to a parameter in a custom query in SQLAlchemy?
  7. How to disable SQLAlchemy caching?
  8. memory-efficient built-in SqlAlchemy iterator/generator?
  9. AttributeError: can’t set attribute when connecting to sqlite database with flask-sqlalchemy
  10. Case Insensitive Flask-SQLAlchemy Query
  11. sqlalchemy unique across multiple columns
  12. How to create a new database using SQLAlchemy?
  13. Bulk Insert A Pandas DataFrame Using SQLAlchemy
  14. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  15. Python, SQLAlchemy pass parameters in connection.execute
  16. How can I use psycopg2.extras in sqlalchemy?
  17. Multi-threaded use of SQLAlchemy
  18. How to get column names from SQLAlchemy result (declarative syntax)
  19. How to discover table properties from SQLAlchemy mapped object
  20. AttributeError: ‘int’ object has no attribute ‘_sa_instance_state’
  21. Dynamic Class Creation in SQLAlchemy
  22. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  23. How do I INSERT INTO t1 (SELECT * FROM t2) in SQLAlchemy?
  24. Creating partial unique index with sqlalchemy on Postgres
  25. SQLAlchemy func.count on boolean column
  26. SQLAlchemy: get Model from table name. This may imply appending some function to a metaclass constructor as far as I can see
  27. pandas to_sql all columns as nvarchar
  28. How to make mysql connection that requires CA-CERT with sqlalchemy or SQLObject
  29. Can’t change system default SQLite binary
  30. add column to SQLAlchemy Table

Leave a Comment