many-to-many - SQLAlchemy:如果你和我在一起，我就和你在一起——再次 self 参照 M2M 关系

Question

我正在尝试在 SQLAlchemy 中创建一个类似于多对多映射的社交网络。也就是说，我有一个 Character 类和一个 character_relations 交叉表来链接 Character 到 Character。到目前为止，这很容易:

character_relationships = Table('character_relationships', Base.metadata,
    Column('me_id', Integer, ForeignKey('characters.id', ondelete=True, onupdate=True), nullable=False, primary_key=True),
    Column('other_id', Integer, ForeignKey('characters.id', ondelete=True, onupdate=True), nullable=False, primary_key=True),
    UniqueConstraint('me_id', 'other_id', name='uix_1')
)

class CharacterRelationship(object):

    def __init__(self, me_id, other_id):
        self.me_id = me_id
        self.other_id = other_id

mapper(CharacterRelationship, character_relationships)

class Character(IdMixin, TimestampMixin, Base):
    __tablename__ = "characters"

    name = Column(Unicode, nullable=False)

    friends = relationship(lambda: Character, secondary=character_relationships, 
             primaryjoin=lambda: Character.id==character_relationships.c.me_id,
             secondaryjoin=lambda: Character.id==character_relationships.c.other_id,
             backref=backref('knows_me')
             )

有两种可能的关系:单边和双边。那是在我可以拥有的交叉表中

CharacterRelationship(me_id=1, other_id=2)
CharacterRelationship(me_id=2, other_id=1)

上面给出的 Character.friends 参数是关于单方面关系的。如何为双边关系添加属性/column_property？

如果关系“站在双方”，则 my_character.real_friends 仅包含来自 CharacterRelationship 的条目。

我知道我可以使用类似的东西

@property
def real_friends(self):
    return set(my_character.friends) & set(my_character.knows_me)

但这并不能很好地转换为 sql，我希望在数据库级别执行此集合交集会有巨大的加速。但还有更多需要实现!

更好的是有一个最终对象，如:

character.friends.other_character
character.friends.relationship_type = -1 | 0 | 1

在哪里

-1 表示我单方面知道其他，

0 双边,

1 表示其他人单方面认识我

您是否有理由将此逻辑置于数据库级别？如果是，你会怎么做？
如果知道，你知道如何把简单的real_friend双边部分放在数据库层面吗？

Answer 1

对于 real_friends，您可能希望在数据库级别进行查询。它应该是这样的:

@property
def real_friends(self):
    char_rels1 = aliased(CharacterRelationship)
    char_rels2 = aliased(CharacterRelationship)
    return DBSession.query(Character).\
        join(char_rels1, char_rels1.other_id == Character.id).\
        filter(char_rels1.me_id == self.id).\
        join(char_rels2, char_rels2.me_id == Character.id).\
        filter(char_rels2.other_id == self.id).all()

您当然可以将其设置为关系。

对于 other_character (我假设这些是非共同 friend )，我会做一个类似的查询，但使用外连接。

对于relationship_type，我会在缓存knows_person() 时执行以下操作:

def knows_person(self, person):
    return bool(DBSession.query(CharacterRelationship).\
        filter(CharacterRelationship.me_id == self.id).\
        filter(CharacterRelationship.other_id == person.id).\
        first())

def rel_type(self, person):
    knows = self.knows_person(person)
    known = person.knows_person(self)
    if knows and known:
        return 0
    elif knows:
        return -1
    elif known:
        return 1
    return None