Michael,
Thank you for the answer, I have been "wasting" my time all day getting this
to work, reading documents etc. Since I'm a low level programmer I'm not really
into database programming, The article you pointed out is not really
understandable by me because of this.
I did the following (I left out the "other" definitions:
class Relation(Base):
__tablename__ = 'relations'
RId = Column(Integer,
ForeignKey('affiliations.Id'), primary_key=True) # primary_key is just to make
SQLA happy about the PK needed
LId = Column(Integer,
ForeignKey('affiliations.Id'))
class Affiliation(Base):
__tablename__ = "affiliations"
Id = Column(Integer,
primary_key=True)
discriminator = Column('type', Unicode(20))
__mapper_args__ = {'polymorphic_on': discriminator}
ParentRelation =
relation('Affiliation',primaryjoin='Relation.LId == Affiliation.Id',
secondary=Relation.__table__)
ChildRelation =
relation('Affiliation',primaryjoin='Relation.RId == Affiliation.Id',
secondary=Relation.__table__)
This trows the error: sqlalchemy.exc.ArgumentError: Could not determine join
condition between parent/child tables on relation Affiliation.ChildRelation.
Specify a 'primaryjoin' expression. If this is a many-to-many relation,
'secondaryjoin' is needed as well.
I tried it with the table definition not declerative:
rel = Table('relations', Base.metadata,
Column('RId', Integer, ForeignKey('affiliations.Id')),
Column('LId', Integer, ForeignKey('affiliations.Id')))
class Affiliation(Base):
__tablename__ = "affiliations"
Id = Column(Integer, primary_key=True)
discriminator = Column('type', Unicode(20))
__mapper_args__ = {'polymorphic_on': discriminator}
ParentRelation =
relation('Affiliation',primaryjoin='relations.LId == Affiliation.Id',
secondary=rel)
ChildRelation =
relation('Affiliation',primaryjoin='relations.RId == Affiliation.Id',
secondary=rel)
And this throws: AttributeError: 'Table' object has no attribute 'LId'
(Once I create an Instance of Company like z=Company(), see the definition
below)
I also did put the relation definition in the __init__ of the Affiliation class
(with :
def __init__(self):
self.ParentRelation =
relation('Affiliation',primaryjoin='Relation.LId == Affiliation.Id',
secondary=Relation.__table__)
self.ChildRelation =
relation('Affiliation',primaryjoin='Relation.RId == Affiliation.Id',
secondary=Relation.__table__)
This seems to work, but:
p1 = Person() (see below)
z = Company() (see below)
Traceback (most recent call last):
File "/Users/martijn/pywebos/Affiliation/AffiliationMain.py", line 126, in
<module>
z.ParentRelation.append(p1)
AttributeError: 'RelationProperty' object has no attribute 'append'
I'm puzzled, sorry....
Any suggestions?
Martijn
On Jan 4, 2011, at 4:30 PM, Michael Bayer wrote:
>
> 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 sqlalch...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> 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 sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> 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 sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> 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 sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
