Hello Simon!
Thank you a lot, your solution was helping me out.
среда, 29 мая 2019 г., 14:38:28 UTC+3 пользователь Simon King написал:
>
> On Wed, May 29, 2019 at 11:46 AM kosta <nau...@gmail.com <javascript:>>
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
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/972e5437-f09a-4a2d-997e-d45e5d374842%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
