Mapper relation's order_by not respected by new objects

[Former user]

In a one-to-many relation, if the parent has a relation to the children with an order_by parameter and new children are added, they are not ordered until they are accessed from the database.

What follows is a test program which displays this behaviour:

from sqlalchemy import *

md = MetaData('test')

class Item(object):
    def __init__(self, title):
        self.title = title

class Subitem(object):
    def __init__(self, item, name):
        self.item = item
        self.name = name

items = Table('items', md, Column('item_id', Integer, primary_key=True), Column('title', String(10)))
subitems = Table('subitems', md, Column('subitem_id', Integer, primary_key=True), Column('item_id', Integer, ForeignKey('items.item_id')), Column('name', String(10)))

engine = create_engine('sqlite:///:memory:')
md.create_all(engine)

session = create_session(bind_to=engine)

mapper(Item, items, properties = { 'subitems': relation(Subitem, order_by=subitems.c.name, backref='item')})
mapper(Subitem, subitems)

item = Item('Item 1')
session.save(item)

s1 = Subitem(item, 'Last')
session.save(s1)

s2 = Subitem(item, 'First')
session.save(s2)

session.flush()

# Out of order!
print [ si.name for si in item.subitems ]

# In order
print [ si.name for si in session.load(Item, 1).subitems ]

[Mike Bayer]

the "order_by" parameter refers to the ORDER BY clause sent to the database in a SELECT, and nothing else. the actual ordering of collections is dependent on the collection class used. by default its just a list, so the default collection ordering is "insert order" (which happens to work nicely with ORDER BY).

a central tenet of SA is that it stays focused on the job of issuing SQL and wants as little to do with the behavior of your actual classes as possible...that behavior is left up to the developer. if youd like your collections to perform some other kind of ordering, create a collection that orders its contents whatever way you want, and specify it on your relation using the "collection_class" parameter to relation().