10

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

skornos
  • 3,121
  • 1
  • 26
  • 30

2 Answers2

8

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)
Nima Soroush
  • 12,242
  • 4
  • 52
  • 53
  • I've solved it the same way already but thanks for your response :) accepting – skornos Sep 29 '14 at 11:27
  • Apparently, the last line does not work. I have to do `friend = Friendship(extra_field=0 , fk_user_to=me , fk_user_from=my_friend)` instead. – Anh Pham Jun 01 '18 at 07:19
  • For me, this raises `sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join ... tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.` – ajwood Nov 25 '18 at 02:23
1

I have answered to a similar question that solves Self-referential many-to-many relationship with Association Object.

See https://stackoverflow.com/a/62281276/9387542

Also, if you solely looking for establing a frienship model, I suggest you to establish Many-to-many relationship with the model from https://stackoverflow.com/a/7317251/9387542

abhijithvijayan
  • 835
  • 12
  • 17