CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
eran
eran

Reputation: 15136

How to create an SQLAlchemy nested filter

Say I have a schema of photo tagging, in postgreSQL.

It is pretty simple:

I'm using SQL alchemy and trying to understand how to compose a filter to find, and delete, all the tags of all the photos of a specific user.

I know how to go about finding all the photos of a user:

specific_user_id = "1234"
DBSession = sessionmaker(bind=engine)
s = DBSession()
q = s.query(Photo).filter(Photo.user_id == specific_user_id)
q.delete()

How do I extend this to get all the tags. I can use a loop:

for photo in q.all():
    q2 = s.query(Tag).filter(Tag.photo_id == photo.photo_id) 
    q2.delete()

But I'm looking to do this without the loop.

Upvotes: 2

Views: 3098

Answers (1)

davidism
davidism

Reputation: 127180

Assuming the models look something like this:

class Photo(Base):
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey(User.id), nullable=False)

    user = relationship(User, backref='photos')

class Tag(Base):
    id = Column(Integer, primary_key=True)
    photo_id = Column(Integer, ForeignKey(Photo.id), nullable=False)

    photo = relationship(Photo, backref='tags')

Each model has a foreign key to their "owner" (user, photo), and a relationship to that model. You can write a query with joins along the relationship to get all the tags for all a user's photos.

tags = session.query(Tag).join(
    Tag.photo, Photo.user
).filter(User.id == specific_user_id)

for tag in tags:
    session.delete(tag)

session.commit()

Use session.delete instead of query.delete because it allows SQLAlchemy to clean up behind the scenes to make sure everything is consistent according to any other relationship rules you defined.

Upvotes: 1

Related Questions