SQLAlchemy - 与额外列的自引用多对多关系 [英] SQLAlchemy - Self referential Many-to-many relationship with extra column

问题描述

我有一个代表用户的模型，我想在代表他们是朋友的用户之间建立关系.我的带有关联表和列出所有朋友的方法的功能模型看起来像这样

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

问题是我需要在确认之前实现询问好友关系和状态待定(就像你从 Facebook 知道的那样)，所以需要在好友关系表中添加一个额外的列.根据 SQLAlchemy 教程，我应该创建一个关联对象但是如何让它再次自引用?

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?

或者是否可以仅将此列添加到我当前的好友表中并以某种方式访问​​和更改状态值?

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

谢谢

推荐答案

你只需要在你的表中添加primaryjoin，并在Friendship 表中创建两个外键，'primary_key'.你还需要把友谊当成一个班级.

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.

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 )

要添加朋友，您需要定义 Friendship，例如:

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

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

这篇关于SQLAlchemy - 与额外列的自引用多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！