Deleting multiple records in a table using join in Peewee?

Question

Since joining is not allowed on "delete" queries in Peewee, what is the best way to delete all records in table_2 that match a specific condition in related table_1?

Using a simple example, I want to achieve the equivalent of this:

  DELETE message.*
  FROM message
  JOIN user ON message.from_user_id = user.id
  WHERE user.name = "Joe";

score 12 · Accepted Answer · answered Oct 24 '15 at 05:47

12

You should use subqueries for this type of thing, e.g.:

joe = User.select().where(User.username == 'Joe')
Message.delete().where(Message.from_user == joe).execute()

Let's say you want to delete all messages from "banned" users. You could write:

banned_users = User.select().where(User.is_banned == True)
Message.delete().where(Message.user.in_(banned_users)).execute()

answered Oct 24 '15 at 05:47

coleifer

24,887
6
60
75

2

This works but I believe the question is how to do a: `delete a from a,b where a.field1=b.field2 where ...` type of query – lanzalibre Oct 26 '15 at 11:08
1

Thank you Coleifer. But indeed, I'm wondering if there's a way to avoid using subqueries? – David Oct 27 '15 at 13:21
From lack of reply, I'll suppose this is not possible. :-) – David Nov 11 '15 at 16:40

score 1 · Answer 2 · answered Jun 28 '16 at 05:53

If you're using Postgresql, you can use a raw query with the USING clause

name_to_delete = 'Joe'
query = Message.raw("""
    DELETE FROM message 
        USING user 
    WHERE 
        message.from_user_id = user.id AND
        user.name = %s
""", name_to_delete)
query.execute()

Deleting multiple records in a table using join in Peewee?

2 Answers2

Linked