SQLAlchemy: Using delete/update with a join query

Question

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.

socrates · Accepted Answer · 2015-08-18 14:58:17Z

6

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.

answered Aug 18, 2015 at 14:58

socrates

1,32111 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pav Sidhu Over a year ago

Is there a way to do this without referring to the user_id column in posts? The example I used is just a common analogy for my actual problem.

socrates Over a year ago

how do you link Post with User, shouldn't Post have a column referred to User?

Pav Sidhu Over a year ago

I have updated the question to fit my issue, I don't think I was clear sorry. would like to delete every question which links to a row in topic which links to a row in subject which has a matching account_id.

JP Hanna · Accepted Answer · 2022-01-19 18:38:02Z

3

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()

answered Jan 19, 2022 at 18:38

JP Hanna

411 silver badge5 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy: Using delete/update with a join query

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related