OK the bad thing is that theres no way ppl are going to be able to
figure out a relation like this one unless I add crazy amounts of
docs...anyway, the answer is:
mapper(Section, section_table, properties = {
'items': relation(Item, backref='section'),
'keywords':relation(Keyword,
primaryjoin=and_(
keyword_table.c.uuid==itemkeyword_table.c.keyword_uuid,
item_table.c.uuid==itemkeyword_table.c.item_uuid,
section_table.c.id==item_table.c.section_id),
viewonly=True, foreign_keys=[keyword_table.c.uuid], remote_side=
[item_table.c.section_id])
}
)
the "foreignkey" argument is deprecated, and in the trunk the entire
system of calculating "what does it mean when we say A relates to B"
has been highly clarified and strictified, as well as the way it
calculates the "lazy clause" which is the part that was breaking for
you. that logic doesnt find the things it wants to in that big join
condition you gave it since it cant reconcile "section_table" against
"keyword_table" in such a way that it also knows where to put the "?"
for the lazy clause (in previous versions, it made a very good guess,
which guessed right for things like the above but guessed wrong for a
lot of other stuff. now it doesnt guess so much). of course the
improvment to make here is to antipicate that particular error and
raise a nicer message earlier on.
anyway in the above, you give it the foreign key (or however many you
want to describe) in foreign_keys, and then to tell the lazy loader
which columns to put the ? in you use remote_side. the above values
are a little artificial to make the right thing happen.
alternatively, you can just force your own lazy loader, perhaps I
should add a recipe for this...its just as functional (except wont
work for an eager load) and is much more blunt in its intention:
from sqlalchemy.orm.session import attribute_manager
def load_keywords(instance):
def lazyload():
session = object_session(instance)
return session.query(Keyword).select(and_(
keyword_table.c.uuid==itemkeyword_table.c.keyword_uuid,
item_table.c.uuid==itemkeyword_table.c.item_uuid,
instance.id==item_table.c.section_id))
return lazyload
attribute_manager.register_attribute(Section, 'keywords',
uselist=True, callable_=load_keywords)
of course thats a variant on whats in the docs, which is just to use
"property" to make your own function (which IMHO makes the "viewonly"
flag not really needed).
On Mar 19, 2007, at 9:56 PM, Steve Zatz wrote:
> import uuid as uuid_
> from sqlalchemy import *
>
> def get_uuid():
> a = str(uuid_.uuid4())
> a = a.strip('{}')
> return a.replace('-','')
>
>
> engine = create_engine('sqlite://')
>
> metadata = BoundMetaData(engine)
> engine.echo = True #True
>
>
> item_table = Table('item',metadata,
> Column('id', Integer, primary_key=True),
> Column('uuid',String(32), unique=True, nullable=False),
> Column('parent_uuid', String(32), ForeignKey
> ('item.uuid'), nullable=True),
> Column('name',String(150)),
> Column('section_id', Integer, ForeignKey('section.id'))
> )
>
> section_table = Table('section', metadata,
> Column('id', Integer, primary_key=True),
> Column('name', String(25), unique=True,
> nullable=False),
> )
>
> keyword_table = Table('keyword', metadata,
> Column('uuid',String(32), primary_key=True),
> Column('name', String(25), unique=True,
> nullable=False)
> )
>
> itemkeyword_table = Table('item_keyword', metadata,
> Column('item_uuid', String(32),ForeignKey
> ('item.uuid'), primary_key=True),
> Column('keyword_uuid', String(32), ForeignKey
> ('keyword.uuid'), primary_key=True),
> )
>
> metadata.create_all()
>
> # class definitions
> class Item(object):
> def __init__(self, name=None, id=None, uuid=None, **kw): #?
> **kw for x,y in kw, setattr(x) = y
> self.name = name
> self.id = id
> if uuid:
> self.uuid = uuid
> else:
> self.uuid = get_uuid()
> for k in kw:
> setattr(self, k, kw[k])
>
>
> def _get_keywords(self):
> return [ik.keyword for ik in self.itemkeywords]
>
> keywords = property(_get_keywords)
>
> class Section(object):
> def __init__(self, name=None, id=None):
> self.name = name
> self.id = id
>
>
> class Keyword(object):
> def __init__(self, name=None, id=None, uuid=None):
> self.name = name
> self.id = id
> if uuid:
> self.uuid = uuid
> else:
> self.uuid = get_uuid()
>
>
> class ItemKeyword(object):
> def __init__(self, keyword=None):
> if keyword:
> self.keyword = keyword
>
>
> mapper(Section, section_table, properties = {'items': relation
> (Item, backref='section'), 'keywords':relation(Keyword,
> primaryjoin=and_
> (keyword_table.c.uuid==itemkeyword_table.c.keyword_uuid,
> item_table.c.uuid==itemkeyword_table.c.item_uuid,
> section_table.c.id==item_table.c.section_id),
> viewonly=True, foreignkey=keyword_table.c.uuid)})
>
> mapper(Keyword, keyword_table)
>
> mapper(Item, item_table, properties = dict(itemkeywords = relation
> (ItemKeyword, lazy=False, backref='item'),
> children = relation(Item, remote_side=[item_table.c.parent_uuid],
> backref=backref('parent', remote_side=[item_table.c.uuid])),
> ))
>
> mapper(ItemKeyword, itemkeyword_table, properties={'keyword':
> relation(Keyword, lazy=False, backref='itemkeywords')})
>
>
> ######################################################################
> ######################################################################
> ##################
> session = create_session(bind_to=engine)
>
> item1 = Item(name="Item 1")
> section1 = Section(name="Section1")
> keyword1 = Keyword(name="Keyword1")
> session.save(item1)
> session.save(section1)
> session.save(keyword1)
> session.flush()
> item1.itemkeywords.append(ItemKeyword(keyword1))
> session.flush()
> section = session.query(Section).select()[0]
> section.keywords #exception occurs
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
