I am trying to use mappers to model a self-referential table with a
two-column primary key (id and version, used to track history).
Here's a minimal example:
#### Begin code ####
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=True)
folder_table = Table('folder', meta,
Column('id', Integer, primary_key=True),
Column('version', Integer, primary_key=True),
Column('name', String),
Column('parent_id', Integer, ForeignKey('folder.id'), nullable=True),
Column('parent_version', Integer, ForeignKey('folder.version'),
nullable=True),
)
meta.create_all()
ins = folder_table.insert()
for id,version,name,parent_id,parent_version in [
(1, 1, 'Top', None, None),
(1, 2, 'Top (renamed)', None, None),
(2, 1, 'Child #1', 1, 1),
(2, 2, 'Child #1', 1, 2),
(3, 1, 'Child #2', 1, 2),
]:
ins.execute(id=id, version=version, name=name,
parent_id=parent_id, parent_version=parent_version)
class Folder(object):
pass
mapper(Folder, folder_table, properties=dict(
child_folders = relation(Folder, foreignkey=folder_table.c.parent_id),
))
ses = create_session()
top_v1, = ses.query(Folder).select_by(name='Top')
print [(f.id,f.version) for f in top_v1.child_folders] # should be
only one child
top_v2, = ses.query(Folder).select_by(id=1, version=2)
print [(f.id,f.version) for f in top_v2.child_folders] # should be two children
#### End code ####
The SQL generated for the child_folders property is not what I think
it should be, and I think that's because I can't pass multiple columns
in the foreignkey parameter to the relation.
Any suggestions? (other than "redesign your tables"... unfortunately I
can't do that).
Chris Perkins
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
