How to create many-to-many relationships in SQLAlchemy (python, flask) for the User to itself model

Question

I need to create a table under the name of friends , it should look like this:

friends:

• user_id
• friend_id

I tried to do this with tutorials from SQLALchemy, but I did not find how to make many-to-many relationships for a single table.

Here is what I tried:

# friends table
# many to many - user - user
_friends = db.Table('friends',
    db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
    db.Column('friend_id', db.Integer, db.ForeignKey('users.id'))
)


class User(db.Model, UserMixin):

    # table name in database
    __tablename__ = 'users'

    # primary key for table in db
    id = db.Column(db.Integer, primary_key=True)

    # email is unique!
    email = db.Column(db.String(255), unique=True)

    # password, max = 255
    password = db.Column(db.String(255))

    # category relation
    categories = relationship("Category")

    # cards relation
    cards = relationship("BusinessCard")

    # friends
    friends = db.relationship(
        'User',
        backref="users",
        secondary=_friends
    )

He says:

AmbiguousForeignKeysError: / User.friends - , "". "foreign_keys", .

- , ?

Answer 1

, , " ". SQLAlchemy , :

http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#adjacency-list-relationships

"remote_side" kwarg.

: , , , ( "" ) , "" : "user_id" "friend_id". SQLAlchemy , , , . , ""

user_id   : 1
friend_id : 2

SQLAlchemy , user_1 user_2 .

, . unijective, user_1, user_2, , user_2 user_1 ; , . , Livejournal, Facebook.

, unijective SQLAlchemy. UNION ALL - MySQL.

Answer 2

.

http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-viii-followers-contacts-and-friends

Answer 3

You cannot directly link many-to-many between two tables, instead you need to use a 3rd table.