How to create an SQLAlchemy nested filter

Question

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

It is pretty simple:

a user has many photos
a photo has many tags

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.

Yep. Photo has user_id as a foreign key, and tag has a photo_id as a foreign key. I just don't know the syntax for such a query in SQLAlchemy — eran
– eran, Commented Jun 1, 2014 at 14:48

davidism · Accepted Answer · 2014-06-01 16:06:31Z

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.

Collectives™ on Stack Overflow

How to create an SQLAlchemy nested filter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related