Simon, thank you a lot for your explanation instead of simple code snippet! I appreciate. I'm working on an app based on the flask framework and each incoming request has own session which automatically removes at the end of the request.
On Tuesday, September 24, 2019 at 6:37:45 PM UTC+3, Simon King wrote: > > Before I answer your question, let me try to explain why your attempt > didn't work. > > SQLAlchemy deliberately ensures that a given session only contains a > single instance corresponding to an entity in the database. So for > example you can run the following: > > userid = '9e53da4b-f506-46b1-a3be-c091585d704c' > user1 = session.query(User).filter(User.id == userid).one() > user2 = session.query(User).filter(User.id == userid).one() > assert user1 is user2 > > user1 and user2 are the same object. > > Now imagine that you have 2 separate parts of your application that > work with User objects. One part is only interested in active > tournaments, and the other part wants *all* tournaments. If they use > the same session, they will both be working with the same User object, > and so "user.member_of" will be incorrect for one part of the > application. > > For this reason, "user.member_of" is by default *not affected* by the > filter conditions in your query. You defined that relationship to > represent *all* tournaments, so that's what SQLAlchemy gives you. > > If you really want to change "user.member_of" to include a subset of > tournament records, you can use "contains_eager" to tell SQLAlchemy to > populate that relationship with your filtered list: > > > https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#contains-eager > > > It would look something like this: > > (session.query(db.User) > .join(db.Player, db.Player.user_id == db.User.id) > .join(db.Tournament, db.Player.tournament_id == db.Tournament.id) > .filter(db.Tournament.is_active == True, db.User.id == > '9e53da4b-f506-46b1-a3be-c091585d704c') > .options(contains_eager("member_of").contains_eager("tournament"))) > > Hope that helps, > > Simon > > On Tue, Sep 24, 2019 at 4:17 PM kosta <nau...@gmail.com <javascript:>> > wrote: > > > > I've created SQL query which excludes not active tournaments, but I > still have no idea how to get a similar result by ORM where the list of > user.member_of keeps only active tournament records... > > select u.*, t.* > > from "user" u > > join playeroftournament pt on pt.user_id = u.id > > join tournament t on pt.tournament_id = t.id > > where u.id = '9e53da4b-f506-46b1-a3be-c091585d704c' and t.is_active = > true; > > > > This query produces results which include all tournaments > > session.query(db.User).join(db.Player, db.Player.user_id == > > db.User.id).join(db.Tournament, > db.Player.tournament_id == db.Tournament.id).filter(db.Tournament.is_active > == True, db.User.id == '9e53da4b-f506-46b1-a3be-c091585d704c') > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > > --- > > You received this message because you are subscribed to the Google > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an email to sqlal...@googlegroups.com <javascript:>. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/08f5c11a-93ce-40a6-abe4-0ec15e38a479%40googlegroups.com. > > > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dd06a5b3-79d1-40bf-9437-e8a677cf4d29%40googlegroups.com.
