How To Create Relationship Many To Many In Sqlalchemy (python, Flask) For Model User To Itself

I need to create a table called friends, it should looks like: friends: user_id friend_id I was trying to do this with tutorials from SQLALchemy, but I have not found how to make

Solution 1:

The pattern you're trying to implement is a special case of many-to-many relationship. SQLAlchemy calls this an Adjacency List Relationship, and I recommend trying to follow through the code there:

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

The key is the 'remote_side' kwarg there.

Here's why: the error that you're getting is because you association table ('friends') has two foreign keys pointing to table 'users': one on column 'user_id', and one on column 'friend_id'. SQLAlchemy tries to auto-detect relationships based on foreign keys, but it fails because it can't tell which direction the relationship goes. So if you have an entry in table 'friends' like so

user_id   :1friend_id :2

SQLAlchemy can't tell whether user_1 has user_2 as a friend, or vice-versa.

If that seems confusing, it is. Friendship in the sense of social networks can be unijective, in which case user_1 having friend user_2 does not mean that user_2 has user_1 as a friend; or it can be bijective, in which case the two are equivalent. I'm showing my age here, but the former is represented by Livejournal, whereas the latter is represented by Facebook.

I don't know off the top of my head how to implement a unijective relationship in SQLAlchemy. It's an ugly UNION ALL or something like that in MySQL.

Solution 2:

Miguel Grinberg's tutorial explain this type of application.

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

Solution 3:

You can't directly make Many-To-Many relation between 2 tables, instead you have to use 3rd table.