SQLAlchemy——与额外列的自引用多对多关系

I have a model representing user and I want to create a relationship between users representing that they are friends. My functional model with association table and methods to list all the friends look like this

我有一个表示用户的模型，我想在表示他们是朋友的用户之间创建一个关系。我使用关联表和列出所有朋友的方法的功能模型如下所示

friendship = db.Table('friend',
    db.Column('id', db.Integer, primary_key=True),
    db.Column('fk_user_from', db.Integer, db.ForeignKey('user.id'), nullable=False),
    db.Column('fk_user_to', db.Integer, db.ForeignKey('user.id'), nullable=False)
)

class User(db.Model):
   ...
   ...
   friends = db.relationship('User',
        secondary=friendship,
        primaryjoin=(friendship.c.fk_user_from==id),
        secondaryjoin=(friendship.c.fk_user_to==id),
        backref = db.backref('friend', lazy = 'dynamic'), 
        lazy = 'dynamic')

    def list_friends(self):
        friendship_union = db.select([
                        friendship.c.fk_user_from, 
                        friendship.c.fk_user_to
                        ]).union(
                            db.select([
                                friendship.c.fk_user_to, 
                                friendship.c.fk_user_from]
                            )
                    ).alias()
        User.all_friends = db.relationship('User',
                       secondary=friendship_union,
                       primaryjoin=User.id==friendship_union.c.fk_user_from,
                       secondaryjoin=User.id==friendship_union.c.fk_user_to,
                       viewonly=True) 
        return self.all_friends

The problem is that I need to implement asking for frienship and status pending before confirmation (just like you know it from Facebook) so it is necesarry to add an extra column to the frienship table. According to the SQLAlchemy tutorial I should create an Association Object but how to make it self referential again?

问题是，在确认之前，我需要实现对frienship和status的请求(就像你在Facebook上知道的那样)，所以需要添加额外的列到frienship表中。根据SQLAlchemy教程，我应该创建一个关联对象，但是如何使它自我引用呢?

Or is it possible to just add this column to my current frienship table and access and change the status value there somehow?

或者，是否可以将这一列添加到我当前的frienship表中并访问并以某种方式更改其中的状态值?

Thanks

谢谢

1 个解决方案

#1

All you need is to add primaryjoin in your table and also making two foreignkey in table of Friendship, 'primary_key' too. you also need to make friendship as a class.

你所需要做的就是在你的表中添加primaryjoin，并且在友情表中添加两个foreignkey，也是primary_key。你也需要把友谊当作一门课。

class Friendship(db.Model):
    __tablename__ = 'friend'
    fk_user_from = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    fk_user_to = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
    extra_field = db.Column(db.Integer)


class User (db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    user_to = db.relationship('Friendship',backref='to', primaryjoin=id==Friendship.fk_user_to)
    user_from = db.relationship('Friendship',backref='from', primaryjoin=id==Friendship.fk_user_from )

And to add a friend you need to define Friendship like:

为了增加一个朋友，你需要定义友谊:

friend = Friendship(extra_field=0 , to=me , from=my_friend)

#1