“Join”在同一选项卡中

Question

我正在使用camelot（使用sqlalchemy ORM）创建一个保存成员列表的程序。会有人结婚，这意味着一对一的关系在同一张桌子。这是Person类的简化版本：

class Person( Entity ):
    id = Column(Integer, primary_key=True)
    firstname = Column( Unicode(30), nullable = False )
    lastname = Column( Unicode(30), nullable = False )
    member_no = Column( Integer )
    marital_status = Column( Unicode(15) )
    marriage_date = Column( Date() )
    married_to = ... # Should be one to one with self

我试过这样做：

married_to = relationship("Person", uselist=False, backref="persons")
married_to_id = Column(Integer, ForeignKey('persons.id'))

但它失败了：

InvalidRequestError: One or more mappers failed to initialize - can't proceed with initialization of other mappers. Original exception was: Person.married_to and back-reference Person.persons are both of the same direction . Did you mean to set remote_side on the many-to-one side ?

我怎么能在同一个表中联接，或者这是我使用的错误方法。如果我把人A和B连在一起，那么人B就会自动地和A连在一起（作为一种婚姻工作-它是双向的…）

我希望我说清楚了。：-/

Answer 1

你想要一个邻接列表关系。这是你的答案 documentation。你知道吗

The relationship() configuration here works in the same way as a “normal” one-to-many relationship, with the exception that the “direction”, i.e. whether the relationship is one-to-many or many-to-one, is assumed by default to be one-to-many. To establish the relationship as many-to-one, an extra directive is added known as remote_side, which is a Column or collection of Column objects that indicate those which should be considered to be “remote

或许可以试试：

married_to = relationship("Person", remote_side=[id])

Answer 2

我不指望我会回答你的问题，但我想指出的是，由于两个人之间婚姻的双向性，你可能需要考虑一个单独的表来容纳婚姻，主键由两个person_id引用组成。您还可以考虑将“开始”和“结束”日期都包含在内，从而在该表中保留一个历史记录（）

Answer 3

如果这些都在同一个表中，则不能使用外键。根据定义，外键是来自其他表的键。你知道吗

我会使用married_to列，这样结婚的人就有了各自的伴侣id。那么你的查询就会变得非常简单。你知道吗