如何在引用相同属性的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.
关系表示两个用户之间的友谊.在网上(在文档和Google中)我都发现了如何建立自我参照的m2m关系,从而以不同的方式区分角色.这意味着,在这种m2m关系中,UserA例如是UserB的老板,因此他在下属"属性或您拥有的内容下列出了他.同样,UserB在上级"下列出UserA.
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'.
这没有问题,因为我们可以通过以下方式向同一表声明一个backref:
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.
无论如何,这是我的问题:如果我想将backref到调用backref的同一个属性,该怎么办?像这样:
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屋!