SQLAlchemy：如果你和我在一起，我就和你在一起 - 再次自引用 M2M 关系

Question

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

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 （我假设这些是非共同的朋友），我会执行类似的查询，但使用外连接。

对于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

Answer 2

定义了您的关系 friends 后，real_friends 很容易完成，如下所示：

@property
def real_friends(self):
    qry = (Session.object_session(self).query(User).
            join(User.friends, aliased=True).filter(User.id == self.id).
            join(User.knows_me, aliased=True).filter(User.id == self.id)
          )
    return qry.all()

，但这会给 User< 带来两个多余的 JOIN 。 /code> 表，所以虽然在我看来，它是一个更好的代码，但它可能不如 Jonathan 的版本，其中使用简单的 JOIN 到 CharacterRelationship 表。

至于您要求的甚至更好选项：这可以再次在如下查询中完成（以@Jonathan的答案作为参考）：

@property
def all_friends(self):
    char_rels1 = aliased(Friendship)
    char_rels2 = aliased(Friendship)
    qry = (Session.object_session(self).query(User, case([(char_rels1.id <> None, 1)], else_=0)+case([(char_rels2.id <> None, 2)], else_=0)).
        outerjoin(char_rels1, and_(char_rels1.friends == User.id, char_rels1.knows_me == self.id)).
        outerjoin(char_rels2, and_(char_rels2.knows_me == User.id, char_rels2.friends == self.id)).
        filter(or_(char_rels1.id <> None, char_rels2.id <> None)) # @note: exclude those that are not connected at all
        )
    return qry.all()

需要注意的事项：

• 过滤器被移至join条件——这对于外连接非常重要，否则结果将是错误的。
• 结果将是 [(User, _code_), ..]，其中 _code_ 为：0 - 未连接、1 - 朋友、2 - 认识我、3 - 真实朋友（双向）