surprise....the latest trunk you can do it:
mapper(Folder, folder_table, properties=dict(
child_folders = relation(Folder,
foreignkey=[folder_table.c.parent_id,
folder_table.c.parent_version],
),
))
your test then works (im pretty sure):
[(2, 1)]
[(2, 2), (3, 1)]
On Jun 14, 2006, at 2:01 PM, Chris Perkins wrote:
> 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
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
