I don't see why the relationship between two Affiliation subclasses can't be self-referencing. You'd add another column to "affiliations" with a name like "related_to", use relationship() with primaryjoin. If you model the relationship as simply Affiliation->Affiliation then that single relationship handles the linkage for all subclass combinations.
If you'd rather have the linkages on the "persons" and "companies" tables, that is also fine, you just need to use two separate relationships to manage each foreign key, one local to "persons" the other local to "companies". Even if you use an association table, as long as the linkages are from Affiliation->Affiliation, no weird gymnastics with "relationtypes" should be needed for persistence/querying. This may or may not be related but this seems to have some elements which resemble a "polymorphic association", which is described at: http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/ , perhaps that can shed some light as well. On Jan 4, 2011, at 7:54 AM, Martijn Moeling wrote: > Update, (Still need help, I'm really puzzled on how to do this) > > since both Person and Company are polymorphic from Affiliation the relations > are not really self referencing, > > So I need help defining the relation and a reference table. The reference > table should however be something like this: > > > > TablenameL LId TablenameR Rid > > person 1 company 2 > company 2 person 3 > > So the reference table "relations" should have a compound key "person:1" to > connect to "company:2" > at the same time "company:2" should connect to "person:3" > > I would like to add something like this to my Affiliation class: > > relationships = > relation(Relation,primaryjoin=or_(and_(Relation.LTable==discriminator,Relation.LId > ==Id),and_(Relation.RTable==discriminator,Relation.RId==Id))) > > I know this is wrong but the reference table should also work backwards. > There will also be an Relationtype indicating > > Company->employee->Person, > Company->customer->Person > Person->friend-Person > > etc. (one Company can have many relations to the same Person, like Companies > can have multiple relations with other companies (like "supplier", > "customer", "partner (like in a project)" etc. etc) > > also Person1->Father->Person2 will be Person2->Son->Person1 when lookup the > other way around. the Relationtype will be a ForeignKey to another table, the > "Order" will be dependent on the "side" of the (current object) Person so > there might be two relations in the Affiliation object . One working on the > "Left" side of the reference table and the other one from the "Right" > > This is just a small piece, in total there are many Classes based on the > Affiliation object so doing it all there would be nice.... > > Thanks for ANY thoughts, doing this right from the beginning helps me a lot. > The definition of Affiliation, Person and Company can be found below. > > Martijn > > On Jan 4, 2011, at 9:55 AM, Martijn Moeling wrote: > >> Hi >> >> I have done the following: >> >> >> class Affiliation(Base): >> __tablename__ = "affiliations" >> Id = Column(Integer, primary_key=True) >> FullName = Column(Unicode(255), index = True) >> discriminator = Column('type', Unicode(20)) >> __mapper_args__ = {'polymorphic_on': discriminator} >> >> >> class Person(Affiliation): >> __tablename__ = 'persons' >> __mapper_args__ = {'polymorphic_identity' : u'person'} >> Id = Column(Integer,ForeignKey('affiliations.Id'), >> primary_key=True) >> >> >> class Company(Affiliation): >> __tablename__ = 'companies' >> __mapper_args__ = {'polymorphic_identity' : u'company'} >> Id = Column(Integer,ForeignKey('affiliations.Id'), >> primary_key=True) >> >> which is straight foreward. >> >> What I want do do now is a bit more troublesome, I have been trying many >> different things but.... >> >> class relation(Base): >> >> Parent = reference to one of the Affiliations >> Child = reference to one of the Affiliations >> Relation_type = column(Integer) (like two >> persons can be Father and Son, Two companies can be supplier and customer) >> >> >> It would be very nice if I can change the Affiliation class to self >> reference M:N >> >> Problem with this is that both Parent and child (or "left and right "side") >> have to be checked and updated >> >> Martijn >> >> -- >> 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. >> > > -- > 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. > -- 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.
