CODEWITHSUNDEEP
pythonsqlsqlalchemy
Peter Smit
Peter Smit

Reputation: 28716

How to query those Objects that are not joined (SQLAlchemy)

I have two models in SQLAlchemy, having a many-to-many relationship

team_user_table = Table('team_user', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('team_id', Integer, ForeignKey('teams.id'))
)

class User(Base):
    """ The SQLAlchemy declarative model class for a User object. """
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)

class Team(Base):
    """ The SQLAlchemy declarative model class for a Team object. """
    __tablename__ = 'teams'
    id = Column(Integer, primary_key=True)
    name = Column(Text, unique=True)
    members = relationship("User",
        secondary=team_user_table,
        backref="memberteams")

I would like to query those users that are not member of a specific team.

In SQL (for example):

SELECT u.id,u.name FROM users u WHERE u.id NOT IN (SELECT tu.user_id FROM team_user tu WHERE tu.team_id=?);

How can I do this in SQLAlchemy?

Upvotes: 2

Views: 198

Answers (2)

Salil
Salil

Reputation: 9722

I believe this should work and it does not use subqueries.

session.query(User).join(team_users_table).filter(team_users_table.team_id != OTHER_TEAM)

Upvotes: 0

jadkik94
jadkik94

Reputation: 7078

This does what you want:

team_id = 1
query = session.query(User.id).filter(~User.memberteams.any(Team.id == team_id))

This is the SQL it outputs (on MySQL):

SELECT users.id AS users_id, users.name AS users_name 
FROM users 
WHERE NOT (EXISTS (SELECT 1 
FROM team_user, teams 
WHERE users.id = team_user.user_id AND teams.id = team_user.team_id AND teams.id = %s))

This is not exactly what your query looks like now, but I think this is the way it should be done. Check the docs for this.

I tested this on MySQL. However, I think it should work on any other.

To have the exact query you are looking for, you might need to look into using subqueries in combination with the filter statement. I think you will need explicit reference to the team_user table in your code to make it work.

Upvotes: 2

Related Questions