Many-to-many, same table, association objects.
Stephen Hansen
I'm trying to get a many-to-many relationship, where each side of that
relationship is the same table, and where there's an association
object between them to hold data about the particular relationship.
I'm using SQLAlchemy 0.5rc4.
From the examples on the documentation, and some Googling, I have a
basic idea of how to a) do a many-to-many relationship on the same
table, and b) use an association object, but when it comes to
combining the two I'm a bit lost.
For example, I have:
dbThing = sql.Table('Thing', DatabaseMetadata,
sql.Column('thing_id', sql.Integer, key='id', primary_key=True,
autoincrement=True),
sql.Column('thing_type', sql.String(20), key='type',
nullable=False, index=True),
)
A 'thing' is actually an abstract sort of item, which other tables
"inherit" from to become more specific kinds of 'things'. I'm wanting
to be able to have arbitrary relationships between things with the
relationships themselves having an arbitrary type.
So one thing might have a "dependent" relationship on another thing.
To deal with the many-to-many relationship, I have:
dbThingLink = sql.Table('ThingLink', DatabaseMetadata,
sql.Column("thing_source", sql.Integer,
sql.ForeignKey(dbThing.c.id, ondelete="CASCADE"), key='source'),
sql.Column("thing_destination", sql.Integer,
sql.ForeignKey(dbAsset.c.id, ondelete="NO ACTION"),
key='destination'),
sql.Column("link_type", sql.String(100), key='type',
nullable=False, default=''),
)
Just how to configure the mappers has be sa bit lost at this point.
Any pointers?
Thanks for any help.
--Stephen
Michael Bayer
The first step would be to configure the inheritance hiearchy of
Things, as I see you have an Asset table in there as well which I'm
assuming is a Thing subclass. The joined table inheritance section
in the docs will explain that. then you just set up relations using
the same techniques as any other relation() setup.
Also, as it turns out I'm working on a project which does self-
referential joins from things to subclasses of things with joined
table inheritance, and you're probably going to want to use the latest
trunk of SQLAlchemy as I've fixed some bugs related to joins of the
form query(SomeSubA).join(SomeOtherSubA). a release will be
forthcoming sometime next week.
Stephen Hansen
> Things, as I see you have an Asset table in there as well which I'm
> assuming is a Thing subclass. The joined table inheritance section
> in the docs will explain that. then you just set up relations using
> the same techniques as any other relation() setup.
Yikes, I goofed on making my post; there's no problem with the
inheritance structure and its not really what I meant to ask about.
That line which referenced an asset table was meant to reference the
thing table.
All the relations happen on the Thing level: there's no relations
between any of the subclasses.
Its all in a single table, just Thing-to-Thing, the mapping of which
is confusing me.
Sorry about my original question having misinformation!
--S
Michael Bayer
so, youre going to say relation(MyThingAssociation) on your Thing
mapper and relation(Thing) on your MyThingAssociation mapper. It
would look like any other association mapping.