On Wed, May 29, 2019 at 11:46 AM kosta <naumo...@gmail.com> 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/CAFHwexdQiyQMk0K8_oAy-b0NbmD5q4_5DKACw6dbA-Sk9a0pOw%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.