Filter query. Exclude some records by related model

[kosta]

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.

[Simon King]

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

[kosta]

Hello Simon!

Thank you a lot, your solution was helping me out.

среда, 29 мая 2019 г., 14:38:28 UTC+3 пользователь Simon King написал: