Greetings list,
I've trying to order a property which is a many-to-many relationship with
an association object, with columns that belong not to the relationship
table but to the associated one.
I think it's better to explain with an example, using the tables of the docs:
items = Table('items', metadata,
Column('item_id', Integer, primary_key=True),
Column('name', String(40)),
)
item_keywords = Table('item_keywords', metadata,
Column('item_id', Integer, ForeignKey('items.item_id')),
Column('keyword_id', Integer, ForeignKey('keywords.keyword_id')),
Column('data', String(40))
)
keywords = Table('keywords', metadata,
Column('keyword_id', Integer, primary_key=True),
Column('name', String(40))
)
And the problem lies in the Item mapper:
mapper(Item, items, properties={
'keywords' : relation(KeywordAssociation, association=Keyword,
order_by=[keywords.c.name], lazy=True)
})
So, given an item object (let's call it item), the idea is that item.keywords
be a ordered list not by item_keywords primary key but by keywords.name. But
this doesn't occur with lazy=False and even the mapper generates an exception
when lazy=True (exception saying that it doesn't find the 'name' column).
¿Is this an error or am I doing something wrong?
Anyway, thanks a lot to Michael for this remarkable piece of software, and for
the fabulous support he gives :)
Attached is a small but complete unit-test which exercises this problem.
Kind regards,
Raul Garcia.
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/sqlalchemy
-~----------~----~----~----~------~----~------~--~---
import testbase
from sqlalchemy import *
class AssociationTest(testbase.PersistTest):
def setUpAll(self):
global items, item_keywords, keywords, metadata, Item, Keyword,
KeywordAssociation
metadata = BoundMetaData(testbase.db)
items = Table('items', metadata,
Column('item_id', Integer, primary_key=True),
Column('name', String(40)),
)
item_keywords = Table('item_keywords', metadata,
Column('item_id', Integer, ForeignKey('items.item_id')),
Column('keyword_id', Integer, ForeignKey('keywords.keyword_id')),
Column('data', String(40))
)
keywords = Table('keywords', metadata,
Column('keyword_id', Integer, primary_key=True),
Column('name', String(40))
)
metadata.create_all()
class Item(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return "Item id=%d name=%s keywordassoc=%s" % (self.item_id,
self.name, repr(self.keywords))
class Keyword(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return "Keyword id=%d name=%s" % (self.keyword_id, self.name)
class KeywordAssociation(object):
def __init__(self, keyword, data):
self.keyword = keyword
self.data = data
def __repr__(self):
return "KeywordAssociation itemid=%d keyword=%s data=%s" %
(self.item_id, repr(self.keyword), self.data)
mapper(Keyword, keywords)
mapper(KeywordAssociation, item_keywords, properties={
'keyword':relation(Keyword, lazy=False)
}, primary_key=[item_keywords.c.item_id, item_keywords.c.keyword_id],
order_by=[item_keywords.c.data])
mapper(Item, items, properties={
'keywords' : relation(KeywordAssociation, association=Keyword,
order_by=[keywords.c.name], lazy=True)
})
def tearDown(self):
for t in metadata.table_iterator(reverse=True):
t.delete().execute()
def tearDownAll(self):
clear_mappers()
metadata.drop_all()
def testassoc_orderby_lazy(self):
sess = create_session()
item1 = Item('item1')
item1.keywords.append(KeywordAssociation(Keyword('red'), 'red_assoc'))
item1.keywords.append(KeywordAssociation(Keyword('blue'), 'blue_assoc'))
sess.save(item1)
sess.flush()
l = sess.query(Item).select()
names = [k.keyword.name for k in l[0].keywords]
self.assert_(names == ['blue','red'])
def testassoc_orderby_eager(self):
sess = create_session()
item1 = Item('item1')
item1.keywords.append(KeywordAssociation(Keyword('red'), 'red_assoc'))
item1.keywords.append(KeywordAssociation(Keyword('blue'), 'blue_assoc'))
sess.save(item1)
sess.flush()
l = sess.query(Item).options(eagerload('keywords')).select()
names = [k.keyword.name for k in l[0].keywords]
self.assert_(names == ['blue','red'])
if __name__ == "__main__":
testbase.main()
