Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
405 views
in Technique[技术] by (71.8m points)

python - 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

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?

Thanks

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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 )

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...