Hi list, my use case is along those lines, I want collections to maintain an ordered list of items. items can belong to many collections. when an item is deleted, I'd like the list of items from the collection to be updated, and ready to be changed again
the comments in the test from the code below show what's go wrong I'm aware of the reorder_on_append option, but the source is discouraging, I'm not even sure it is the way to go reorder_on_append Default False. When appending an object with an existing (non-None) ordering value, that value will be left untouched unless ``reorder_on_append`` is true. This is an optimization to avoid a variety of dangerous unexpected database writes. so, my questions are : - is there a way to obtain the expected behaviour ? how? thanks NIL --------------------------------------------------------------------- # -*- coding: utf-8 -*- from nose.plugins.attrib import attr import unittest from sqlalchemy import (create_engine, Column, Integer, String, ForeignKey, ) from sqlalchemy.orm import relationship from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.associationproxy import AssociationProxy from sqlalchemy.ext.orderinglist import ordering_list from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() engine = create_engine('sqlite:///sqlaordering.db') Session = sessionmaker(bind=engine) session = Session() from sqlalchemy.orm.interfaces import MapperExtension class ReorderCollection(MapperExtension): def after_delete(self, mapper, connection, instance): # this isn't efficient at this stage instance.collection._item_orders.reorder() class Item(Base): __tablename__ = 'items' id = Column(Integer, primary_key=True) name = Column(String(50)) _item_orders = relationship("ItemOrder", cascade='all, delete-orphan', backref='item', ) def __init__(self, name): self.name = name def __repr__(self): return '<%r %r>' \ % (self.__class__.__name__.capitalize(), self.name) def _create_item_order(item): return ItemOrder(item=item) class Collection(Base): __tablename__ = 'collections' id = Column(Integer, primary_key=True) name = Column(String(50)) _item_orders = relationship("ItemOrder", cascade='all, delete-orphan', backref='collection', order_by="ItemOrder.position", collection_class=ordering_list('position'), ) items = AssociationProxy("_item_orders", "item", creator=_create_item_order) def __init__(self, name): self.name = name def __repr__(self): return '<%r %r>' \ % (self.__class__.__name__.capitalize(), self.name) class ItemOrder(Base): __tablename__ = 'item_orders' id = Column(Integer, primary_key=True) item_id = Column(Integer, ForeignKey('items.id')) collection_id = Column(Integer, ForeignKey('collections.id')) position = Column(Integer) __mapper_args__ = {'extension': ReorderCollection()} class TestOrderedRelation(unittest.TestCase): def setUp(self): """Method used to build a database""" Base.metadata.create_all(engine) session.add_all([Item('T-Shirt'), Item('Mug'), Item('Hat'), Item('Shoes'), Collection('first'), Collection('second'), ]) session.commit() def tearDown(self): """Method used to destroy a database""" Base.metadata.drop_all(engine) @attr('do_it') def test_problem(self): """ """ first = session.query(Collection).first() shirt = session.query(Item).get(1) mug = session.query(Item).get(2) hat = session.query(Item).get(3) shoes = session.query(Item).get(4) first.items.append(shirt) first.items.append(mug) first.items.append(mug) first.items.append(shoes) session.commit() assert first.items == [shirt, mug, mug, shoes] session.delete(mug) session.commit() # it seems nice assert first.items == [shirt, shoes] first.items.append(hat) assert first.items == [shirt, shoes, hat] # but at this point the positions are wrong assert session.query(ItemOrder.position).all() == [(0,), (3,), (2,)] session.commit() # the list 'magically' changed (no magic, just wrong index) self.assertFalse(first.items == [shirt, shoes, hat]) # hat took index from len of list when it was inserted assert first.items == [shirt, hat, shoes] # worse indices are still wrong assert session.query(ItemOrder.position).all() == [(0,), (3,), (2,)] # until we notify the collection of the change first._item_orders.reorder() assert session.query(ItemOrder.position).all() == [(0,), (2,), (1,)] -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@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.