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 <[email protected]> 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 [email protected].
> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexddLCA%2Bda8kZqQKb5RdzJdAR4mtGg6CDmcDMDWBw-jjSQ%40mail.gmail.com.
