On Nov 18, 2010, at 9:32 AM, Torsten Landschoff wrote: > Hi *, > > I am fighting half a day with something I expected to be trivial: Keep > the order of items in a collection implemented vi a secondary table > (many-to-many relationship). > > Basically, I have a Collection class with a relationship to Items in the > collection. That relationship is configured via > > items=relation(Item, > secondary=collection_item_table, > order_by=[collection_item_table.c.item_order]) > > Now my problem is: How to update the item_order column in the table? So > far I did not find any way to do that. For a many-to-one relationship, > orderinglist will do this just fine.
this is correct. The functionality provided by "secondary" is that SQLA will maintain a table with foreign keys to the related primary keys on either side. It does not do anything at all with additional columns on the "secondary" table. If your "secondary" table has additional columns you need to deal with, you no longer use "secondary" and instead use the association object pattern : http://www.sqlalchemy.org/docs/orm/relationships.html#association-object . To make this pattern act more like "secondary" in the usual case, you use associationproxy: http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html You could most likely use the existing orderinglist extension in conjunction with associationproxy to maintain the ordering you want, in Python. > > I tried using a MapperExtension that goes through the relevant rows in > the secondary table and updates the item_order column (code attached). > It turns out that the after_insert and after_update extension points are > called before the child collections are flushed, so the primary keys of > any new items are not available at that time. Apart from that, it is a > bit heavy on the database to update all the item rows for a selection on > each change... MapperExtension is never the right place to do anything involving relationships. SessionExtension is where you'd organize everything before or after the entire flush occurs. In this case its likely much easier to manipulate the "collection_item_table" directly before any flush happens. > > Another approach I tried was to use replace the relationship via custom > code querying the relation when reconstructing a collection instance. > The loading part works fine but I failed in populating the > collection_item_table in the first place, as the items must be flushed > before the Collection for the item_id to be available and I did not find > a way to tell SQLAlchemy of this dependency. > > Any hint on how to do this is greatly appreciated. > > Greetings, Torsten > > -- > DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH > Torsten Landschoff > > Office Dresden > Tel: +49-(0)351-4519587 > Fax: +49-(0)351-4519561 > > mailto:torsten.landsch...@dynamore.de > http://www.dynamore.de > > Registration court: Mannheim, HRB: 109659, based in Karlsruhe, > Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > <mapper_extension.py><session_extension.py> -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
