Ok it looks good... I assume it also means I can't rely on ON DELETE CASCADE anymore, and I then must use passive_deletes=False. At least on those relations.
In a general point of view, using MySQL InnoDB tables, is ON DELETE CASCADE more efficient than SA stuff? Thanks a lot for your help On 23 oct, 04:00, Michael Bayer <[EMAIL PROTECTED]> wrote: > I do this kind of thing...theres lots of variants on how to do this > but the idea of __after_flush__ is that everything, including > collections, have been committed (but the new, dirty and deleted lists > have not been reset yet): > > class OnFlushExt(orm.SessionExtension): > def after_flush(self, session, flush_context): > for obj in list(session.new) + list(session.dirty) + > list(session.deleted): > if hasattr(obj, '__after_flush__'): > obj.__after_flush__(session.connection()) > > class MyObject(object): > def __after_flush__(self, connection): > connection.execute( > mytable.update().where(mytable.c.id==self.id).\ > values(object_count= > > select > ([func > .count(child_table.c.id)]).where(child_table.c.parent_id==mytable.c.id) > ) > ) > > you could also batch all the updates into a single UPDATE statement > within after_flush(), that would be a lot more efficient (i.e. where > mytable.c.id.in_([all the ids of the changed parent objects]) ). > > On Oct 22, 2008, at 5:41 PM, GustaV wrote: > > > > > Ok... I'm not sure to understand the way you do it... > > > Does it mean I should check the add and delete of regions in the > > transaction in after_flush() and issue sql directly (not using the > > ORM) to update the count in Country? > > > On 22 oct, 20:46, Michael Bayer <[EMAIL PROTECTED]> wrote: > >> On Oct 22, 2008, at 2:05 PM, GustaV wrote: > > >>> Hi all! > > >>> In a 1-N relation between a country and its regions, I'm using an > >>> attribute extension to update the current count of regions into > >>> countries. It works very well when I append or remove regions from > >>> country. > >>> But if I delete one of the region directly (session.delete(region)), > >>> the country doesn't know it has lost one... > > >>> I tried to use a MapperExtension.before_delete to manually remove > >>> the > >>> said region from the country, but it is not marked as "dirty" and > >>> then > >>> not updated... > > >>> Any way to do it properly? > > >> for a one-to-many relation, its often easy enough to just have a > >> cascade rule from country->region such that region is deleted > >> automatically when removed from the parent. This is the typical way > >> to go about deletions from relations, since session.delete() does not > >> cascade "backwards" to all owning collections. > > >> although when I deal with columns that "count" something that is > >> elsewhere represented in the database, I often issue these using SQL > >> within a SessionExtension.after_flush(). This removes the need to > >> worry about catching attribute events and just directly sets the > >> correct value based on the state of the transaction post-flush. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
