On Feb 13, 2009, at 1:46 PM, Stephen Hansen wrote:
>
> Hi, all.
>
> 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?
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.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
