4

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?

Marriott81
  • 275
  • 2
  • 16
vromanch
  • 939
  • 10
  • 22

3 Answers3

8

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.

Channing Moore
  • 478
  • 3
  • 7
6

Miguel Grinberg's tutorial explain this type of application.

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

Ang
  • 545
  • 5
  • 13
1

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

user1587985
  • 665
  • 1
  • 6
  • 13