CODEWITHSUNDEEP
pythondatabaseflasksqlalchemyflask-sqlalchemy
Pav Sidhu
Pav Sidhu

Reputation: 6944

SQLAlchemy: Using delete/update with a join query

Using Flask-SQLAlchemy, I would like to delete rows in table Questions based on the values of another table which is linked to table Topic which then is linked to table Subject with foreign keys. I tried this query:

db.session.query(Questions).join(Topic)join(Subject).filter(Subject.account_id==current_user.id).delete()

However, I receive an error:

InvalidRequestError: Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called

So from this I suppose I cannot use .delete() with .join()

Is there a workaround for this issue? Thanks.

Upvotes: 13

Views: 12417

Answers (2)

JP Hanna
JP Hanna

Reputation: 31

Based on similar discussion in Update joined table via SQLAlchemy ORM using session.query I was able to figure out a workaround if you're using ORM instead of Core. Break the joins out into a separate cte/subquery, which return the ids for the rows you want to update.

question_joins = db.session.query(Question).join(Topic)join(Subject)
question_filter  = question_joins.filter(Subject.account_id==current_user.id)
question_id_subquery = question_filter.with_entities(Question.id).subquery()

db.session.query(Question).filter(Question.id.in_(question_id_subquery)).delete()

Upvotes: 2

socrates
socrates

Reputation: 1323

You don't have to use join for your query, you may done it somehow like

db.session.query(Post).filter(Post.user_id==current_user.id).delete()

Assuming your Post have a user_id column.

Join tables would not know which table to delete, Post or User, because it actually have a convoluted middle table constructed, and query from it.

Upvotes: 6

Related Questions