One to Many relationship of the same class as the parent class

[Stefan]

I have been struggeling for a few days with this now and trying to see
if I maybe can get some help here. I'm using SQLAlchemy with Flask

This is what I have tried so far:

I got a user class defined like this:

association_table = db.Table('association',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('friend_id', db.Integer, db.ForeignKey('friend.id'))
)

class Friend(db.Model):
id = db.Column(db.Integer, primary_key=True)

class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(20), unique=True)
password = db.Column(db.String(30))
friends = db.relationship("Friend",
secondary=association_table)



Basically I want to have a relationship to other objects of class User
in the field User.friends

What am I doing wrong here?

Thanks,
Stefan

[Stefan]

I managed to figure this one out my self :)

 

association_table = db.Table('association',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),

    db.Column('friend_id', db.Integer, db.ForeignKey('user.id'))
)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(20), unique=True)
    password = db.Column(db.String(30))

    email = db.Column(db.String(45), unique=True)
    friends = db.relationship("User",
                secondary=association_table,
                backref='added_by',
                primaryjoin=id == association_table.c.user_id,
                secondaryjoin=id == association_table.c.friend_id)

With this I can now do following:
>>> user1 = User.query.filter_by(id=1).first()
>>> user1.friends
[]
>>> user2 = User.query.filter_by(id=2).first()
>>> user1.friends.append(user2)
>>> user1.friends
[<User('user1','us...@admin.com','2')>]
>>> user1.friends[0].added_by
[<User('admin','ad...@admin.com','1')>]