Filter m2m (exclude some related records)

[kosta]

Hello everyone!

I can't resolve my issue by myself, I really have no idea how to filter desired results, I've m2m relationships between User and Tournament models via table PlayerOfTournament, sometime I need to get User model which will include a list of all related tournaments except not active ones. My weakness is that I can't even to construct pure sql query :(

class Tournament(BaseMixin, TimeCreatedMixin, TimeUpdateMixin, IsActiveMixin, Base):

    players = relationship('Player', back_populates='tournament')

class Player(TimeCreatedMixin, TimeUpdateMixin, Base):

    __tablename__ = 'playeroftournament'

    user_id = Column(UUID(as_uuid=True), ForeignKey('user.id', ondelete='SET NULL'), primary_key=True)

    tournament_id = Column(UUID(as_uuid=True), ForeignKey('tournament.id', ondelete='SET NULL'), primary_key=True)

class User (BaseMixin,  IsActiveMixin, TimeCreatedMixin, TimeUpdateMixin, Base):

    member_of = relationship('Player', back_populates='user')

Could anyone to help with ORM query or sql statement?!

Many thanks in advance.

[kosta]

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')

[Simon King]

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

> --
> 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+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/08f5c11a-93ce-40a6-abe4-0ec15e38a479%40googlegroups.com.

[kosta]

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 Tue, Sep 24, 2019 at 4:17 PM kosta <nau...@gmail.com> 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.