How to create relationship many to many in SQLAlchemy (python, flask) for model User to itself

Question

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 relation many-to-many for same table.

Here's what I have 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
    )

it says:

AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship User.friends - there are multiple foreign key paths linking the tables via secondary table 'friends'. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables.

does anyone know how to do that properly?

Answer 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   : 1
friend_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.