I'm trying to map a single relation onto multiple properties.
Let's say I'm tracking "ideas", and a numbered list of pros and
cons for each idea:
Idea: beer
Pros:
1. tastes great
2. less filling
Cons:
1. warning: may not be true
Or in SQL:
Table('Ideas', metadata,
Column('id', Integer, primary_key=True),
Column('text', String))
Table('Analysis', metadata,
Column('id', Integer, primary_key=True),
Column('idea_id', Integer, ForeignKey('Ideas.id')),
Column('type', String(1)),
Column('position', Integer),
Column('text', String))
Pros and cons are stored in the analysis table, and have a 'P'
or 'C' type indicator. I'd like to do a polymorphic mapping on
the 'type' column, and have separate properties on the 'Idea'
class for each type of analysis, e.g.:
i = Idea()
i.pros = [Pro('tastes great'), Pro('less filling')]
i.cons = [Con('warning: may not be true')]
...and have the 'position' attribute maintained automatically by
its index in the Python list.
The first approach I took was with two relations:
# ... polymorphic mappings for Pro and Con ...
mapper(Idea, idea_table, properties={
'pros': relation(Pro, lazy=False,
primaryjoin=and_(ideas_table.c.id==ana_table.c.idea_id,
ana_table.c.type=='P'),
order_by=[ana_table.c.position],
collection_class=OrderingList),
'cons': relation(Con, lazy=False,
primaryjoin=and_(ideas_table.c.id==ana_table.c.idea_id,
ana_table.c.type=='C'),
order_by=[ana_table.c.position],
collection_class=OrderingList)
})
That works, but needs two separate JOINs to load both types. I
always want both types of analysis if I want any at all, so I
want to take the join load off the database and move it into
Python. I was inspired by the associationproxy approach of
providing an enhanced view on top of a regular SA relation
property:
mapper(Idea, idea_table, properties={
'analysis': relation(Analysis, lazy=False) # load all types
})
class Idea(object):
# some kind of mapping to 'analysis', e.g.
pros = some_magic_property_for('analysis')
cons = some_magic_property_for('analysis')
What I've done to use a single relation is working, but is much
more complicated than separate relations, mostly because of the
constraint that ordering attributes be synced with the Python
list. I'm wondering if maybe there isn't an alternate approach.
The 'analysis' relation is backed by a dict-like
collection_class keyed by the type of instance, and storing them
in a special list type that updates the ordering attribute.
'analysis' isn't accessed directly by user code.
# looks kinda like
an_idea.analysis == \
{ <type 'Pro'>: [<Pro>, <Pro>], <type 'Con'>: [<Con>] }
Then I'm mapping 'pros' and 'cons' properties on the Idea class
to connect to the right partition on the 'analysis' collection.
This does work, but because relation updates are happening
outside of the InstrumentedList (i.e. not on 'analysis' directly),
I'm losing the events that would normally be triggered. I don't
think I can manually manage them either, as they're private
__methods on InstrumentedList.
Anyone mapping anything similar, or have any thoughts about
other approaches?
-jek
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
