On Wed, May 29, 2019 at 11:46 AM kosta <[email protected]> wrote:
>
> Hi everyone!
>
> I can't solve my issue by myself, could anyone has advice on this.
>
>
> I've models (simplified):
> class Tournament(Base):
> id = Column(UUID(as_uuid=True), primary_key=True)
> owner_id = Column(UUID(as_uuid=True), ForeignKey('user.id',
> ondelete='CASCADE'), nullable=False)
>
> owner = relationship('User', back_populates='ownership', cascade='all,delete')
> players = relationship('PlayerOfTournament', back_populates='tournament',
> cascade='all,delete')
>
> class PlayerOfTournament(Base):
> __tablename__ = 'playeroftournament'
>
> user_id = Column(UUID(as_uuid=True), ForeignKey('user.id',
> ondelete='CASCADE'), primary_key=True)
> tournament_id = Column(UUID(as_uuid=True), ForeignKey('tournament.id',
> ondelete='CASCADE'), primary_key=True)
> user = relationship('User', back_populates='member_of', uselist=False,
> cascade='all,delete')
> tournament = relationship('Tournament', back_populates='players',
> uselist=False, cascade='all,delete')
> rank = Column(Integer)
>
> class User(Base):
> id = Column(UUID(as_uuid=True), primary_key=True)
> ownership = relationship('Tournament', back_populates='owner',
> cascade='all,delete')
> member_of = relationship('PlayerOfTournament', back_populates='user',
> cscade='all,delete')
> username = Column(String(96))
>
>
> I would to get all tournaments, with filter by some_user not in players.
> I've no idea how construct query, I tried something like
> session.query(Tournament).filter(Tournament.players.user.notin_(user.id)),
> but it doesn't work.
This is a little tricky. My first response was going to be something like:
session.query(Tournament).outerjoin("players").filter(Player.user_id != user.id)
...but that's wrong because it will include tournaments that the user
played in, as long as another user also played in them.
I *think* something like this should work, but I haven't tested it:
session.query(Tournament).filter(~Tournament.players.any(Player.user_id
== user.id))
See the docs at https://docs.sqlalchemy.org/en/13/orm/tutorial.html#using-exists
(Note that we're filtering on Player.user_id, rather than User.id,
because it saves us having to join to the User table)
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAFHwexdQiyQMk0K8_oAy-b0NbmD5q4_5DKACw6dbA-Sk9a0pOw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.
