如何在 SQLAlchemy ORM 上实现自引用多对多关系，反向引用相同的属性? [英] How can I achieve a self-referencing many-to-many relationship on the SQLAlchemy ORM back referencing to the same attribute?

问题描述

我正在尝试使用 SQLAlchemy 上的声明来实现自引用的多对多关系.

I'm trying to implement a self-referential many-to-many relationship using declarative on SQLAlchemy.

这种关系代表了两个用户之间的友谊.我在网上找到了(在文档和谷歌中)如何建立自我参照的 m2m 关系，其中以某种方式区分角色.这意味着在这种 m2m 关系中，用户 A 是，例如，用户 B 的老板，因此他将他列在下属"属性下或您拥有的属性下.以同样的方式，用户 B 在上级"下列出用户 A.

The relationship represents friendship between two users. Online I've found (both in the documentation and Google) how to make a self-referential m2m relationship where somehow the roles are differentiated. This means that in this m2m relationships UserA is, for example, UserB's boss, so he lists him under a 'subordinates' attribute or what have you. In the same way UserB lists UserA under 'superiors'.

这没有问题，因为我们可以通过这种方式声明对同一个表的反向引用:

This constitutes no problem, because we can declare a backref to the same table in this way:

subordinates = relationship('User', backref='superiors')

当然，'superiors' 属性在类中并不明确.

So there, of course, the 'superiors' attribute is not explicit within the class.

无论如何，这是我的问题:如果我想反向引用到我调用反向引用的相同属性怎么办?像这样:

Anyway, here's my problem: what if I want to backref to the same attribute where I'm calling the backref? Like this:

friends = relationship('User',
                       secondary=friendship, #this is the table that breaks the m2m
                       primaryjoin=id==friendship.c.friend_a_id,
                       secondaryjoin=id==friendship.c.friend_b_id
                       backref=??????
                       )

这是有道理的，因为如果 A 和 B 成为朋友，关系角色是相同的，如果我调用 B 的朋友，我应该得到一个包含 A 的列表.这是完整的有问题的代码:

This makes sense, because if A befriends B the relationship roles are the same, and if I invoke B's friends I should get a list with A in it. This is the problematic code in full:

friendship = Table(
    'friendships', Base.metadata,
    Column('friend_a_id', Integer, ForeignKey('users.id'), primary_key=True),
    Column('friend_b_id', Integer, ForeignKey('users.id'), primary_key=True)
)

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)

    friends = relationship('User',
                           secondary=friendship,
                           primaryjoin=id==friendship.c.friend_a_id,
                           secondaryjoin=id==friendship.c.friend_b_id,
                           #HELP NEEDED HERE
                           )

抱歉，如果文字太多，我只想尽可能地明确.我似乎无法在网络上找到任何参考资料.

Sorry if this is too much text, I just want to be as explicit as I can with this. I can't seem to find any reference material to this on the web.

推荐答案

这是我今天早些时候在邮件列表中暗示的 UNION 方法.

Here's the UNION approach I hinted at on the mailing list earlier today.

from sqlalchemy import Integer, Table, Column, ForeignKey, 
    create_engine, String, select
from sqlalchemy.orm import Session, relationship
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

friendship = Table(
    'friendships', Base.metadata,
    Column('friend_a_id', Integer, ForeignKey('users.id'), 
                                        primary_key=True),
    Column('friend_b_id', Integer, ForeignKey('users.id'), 
                                        primary_key=True)
)


class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    # this relationship is used for persistence
    friends = relationship("User", secondary=friendship, 
                           primaryjoin=id==friendship.c.friend_a_id,
                           secondaryjoin=id==friendship.c.friend_b_id,
    )

    def __repr__(self):
        return "User(%r)" % self.name

# this relationship is viewonly and selects across the union of all
# friends
friendship_union = select([
                        friendship.c.friend_a_id, 
                        friendship.c.friend_b_id
                        ]).union(
                            select([
                                friendship.c.friend_b_id, 
                                friendship.c.friend_a_id]
                            )
                    ).alias()
User.all_friends = relationship('User',
                       secondary=friendship_union,
                       primaryjoin=User.id==friendship_union.c.friend_a_id,
                       secondaryjoin=User.id==friendship_union.c.friend_b_id,
                       viewonly=True) 

e = create_engine("sqlite://",echo=True)
Base.metadata.create_all(e)
s = Session(e)

u1, u2, u3, u4, u5 = User(name='u1'), User(name='u2'), 
                    User(name='u3'), User(name='u4'), User(name='u5')

u1.friends = [u2, u3]
u4.friends = [u2, u5]
u3.friends.append(u5)
s.add_all([u1, u2, u3, u4, u5])
s.commit()

print u2.all_friends
print u5.all_friends

这篇关于如何在 SQLAlchemy ORM 上实现自引用多对多关系，反向引用相同的属性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！