I am trying to use SqlAlchemy to create Sqlite database tables inside Pylons. I use a declarative database to create a table, class and map immediately with the following code:
class Friends(Base): __tablename__ = 'friends' left_id = Column(Integer, ForeignKey('facebooks.id'), primary_key=True) right_id = Column(Integer, ForeignKey('facebooks.id'), primary_key=True) def __repr__(self): return "<Friend(id:'%s' id: '%s')>" % (self.left_id, self.right_id) class Facebook(Base): __tablename__ = 'facebooks' id = Column(Integer, primary_key=True) friends = relationship("Facebook", secondary=Friends.__tablename__, primaryjoin= id == Friends.right_id, secondaryjoin= Friends.left_id == id) def __init__(self, id): self.id = id def __repr__(self): return "<User(id:'%s')>" % (self.id)
I just study all the various relationships , like many to one, from one to many, one to one, and many of them are many and how to implement each with tables and / or declaratively. I am wondering how do I link an object to myself? For example, I want to link facebook with other facebook. In other words, build bonds between them and establish them as "friends." How can I structure the database to make this possible?
Edit: I changed the code I updated above and added an association object called Friends, but when I add a friend to the facebook object, it works in only one direction. If I add Bob as a friend to John, I can see Bob with John. Friends, but I don’t see John in Bob. Friends. What am I doing wrong? I tried to add the following relationships to the Friends class:
friend = relationship("Facebook", backref="friends")
but I get an error:
sqlalchemy.exc.ArgumentError: May not define a join condition between parent / child tables in Friends.friend relationships. Specify the expression 'primaryjoin'. If a "secondary" is present, a "secondaryjoin" is also needed.