Re: [SQLObject] SQLObject delete queries

[Ian B. <ia...@co...>]

On Thu, 2003-07-31 at 18:28, Dave Poirier wrote:
> How could one proceed to delete multiple records out of a database with
> a single query?  I've been trying various methods but I seem to always
> end up with an "unhashable" error.
> 
> desired query: DELETE * FROM accounts WHERE record_expiration <= NOW();
>
> attempted SQLObject code:
> 
> Accounts.delete( (Accounts.q.record_expiration <= func.NOW()) )

.delete() only supports getting a single id, which is why this isn't
working for you.  Basically deletes are hard, because there's a chance
you might be deleting an instantiated object, and the object should know
that it's been deleted.  So you can't get the query you want, and in
general won't be able to.

> the only way I've been able to delete the records was true a rather more
> db intensive method:
> 
> for account in Accounts.select( (Accounts.q.record_expiration <=
> func.NOW()) ):
>   Accounts.delete( account.id )

Yes, that's pretty much the way.  Ideally .delete() should be able to
take a list of ids and delete them all, checking the cache for any
objects but not fetching rows that haven't been instantiated.  Then
.select() should have an option for only returning IDs, and not
instantiating objects either.  This would allow you to do the Right
Thing while still keeping the number of queries down.

At least until you start considering transactions, but I don't quite
know what to think about deletes and transactions at this point.

  Ian