SQLAlchemy declarative many-to-many self-join via Association object

Question

I have a table Users and a table Friends which maps users to other users as each user can have many friends. This relation is obviously symmetric: if user A is a friend of user B then user B is also a friend of user A, I only store this relation once. The Friends table has additional fields besides the two User ID's so I have to use an association object.

I am trying to define this relationship in declarative style in the Users class (which extends the declarative base), but I can't seem to figure out how to do this. I want to be able to access all friends of a given user via a property friends, so say friends = bob.friends.

What's the best approach for this problem? I tried to many different setups to post here, and none of them worked for various reasons.

EDIT: My latest attempt looks like this:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)

    # Relationships
    friends1 = relationship('Friends', primaryjoin=lambda: id==Friends.friend1ID)
    friends2 = relationship('Friends', primaryjoin=lambda: id==Friends.friend2ID)


class Friends(Base):
    __tablename__ = 'friends'
    id = Column(Integer, primary_key=True)
    friend1ID = Column(Integer, ForeignKey('users.id') )
    friend2ID = Column(Integer, ForeignKey('users.id') )
    status = Column(Integer)

    # Relationships
    vriend1 = relationship('Student', primaryjoin=student2ID==Student.id)
    vriend2 = relationship('Student', primaryjoin=student1ID==Student.id)

This however results in the following error:

InvalidRequestError: Table 'users' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

I must admit that at this point I am thoroughly confused because of many failed attempts and might have made more than one stupid mistake in the above.

Answer 1

As mentioned in the comment, I prefer the extended model where the Friendship is an entity on its own and the links between friends are yet separate entities. In this way one can store the properties which are symmetrial as well as assymetrical ones (like what one person thinks about the other). As such the model below should show you what I mean:

...
class User(Base):
    __tablename__ =  "user"

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)

    # relationships
    friends = relationship('UserFriend', backref='user',
            # ensure that deletes are propagated
            cascade='save-update, merge, delete',
    )

class Friendship(Base):
    __tablename__ =  "friendship"

    id = Column(Integer, primary_key=True)
    # additional info symmetrical (common for both sides)
    status = Column(String(255), nullable=False)
    # @note: also could store a link to a Friend who requested a friendship

    # relationships
    parties = relationship('UserFriend', 
            back_populates='friendship',
            # ensure that deletes are propagated both ways
            cascade='save-update, merge, delete',
        )

class UserFriend(Base):
    __tablename__ =  "user_friend"

    id = Column(Integer, primary_key=True)
    friendship_id = Column(Integer, ForeignKey(Friendship.id), nullable=False)
    user_id = Column(Integer, ForeignKey(User.id), nullable=False)
    # additional info assymmetrical (different for each side)
    comment = Column(String(255), nullable=False)
    # @note: one could also add 1-N relationship where one user might store
    # many different notes and comments for another user (a friend)
    # ...

    # relationships
    friendship = relationship(Friendship,
            back_populates='parties',
            # ensure that deletes are propagated both ways
            cascade='save-update, merge, delete',
        )

    @property
    def other_party(self):
        return (self.friendship.parties[0] 
                if self.friendship.parties[0] != self else
                self.friendship.parties[1]
                )

    def add_friend(self, other_user, status, comment1, comment2):
        add_friendship(status, self, comment1, other_user, comment2)

# helper method to add a friendship
def add_friendship(status, usr1, comment1, usr2, comment2):
    """ Adds new link to a session.  """
    pl = Friendship(status=status)
    pl.parties.append(UserFriend(user=usr1, comment=comment1))
    pl.parties.append(UserFriend(user=usr2, comment=comment2))
    return pl

In this way, adding a friendship is pretty easy.
So is updating any attributes of it. You can create more helper methods like add_friend.
With the cascade configuration above also deleting a User or Friendship or UserFriend will make sure that both sides are deleted.
Selecting all friends is as straighforward as you want: print user.friends

The real problem with this solution is to ensure that there are exactly 2 UserFriend links for each Friendship. Again, when manipulating the objects from the code it should not be a problem, but database might potentially be inconsistent if someone imports/manipulates some data directly in the SQL side.

Answer 2

I had this error using Flask-SQLAlchemy, but the other solutions didn't work.

The error only occurred on our production server, whilst everything ran fine on my computer and on the test server.

I had a 'Model' class that all my other database classes inherited from:

class Model(db.Model):

    id = db.Column(db.Integer, primary_key=True)

For some reason, the ORM gave classes that inherited from this class the same table name as this class. That is, for every class it tried to make a table for it called the table 'model'.

The solution was to explicitly name child-tables with the 'tablename' class variable:

class Client(Model):

    __tablename__ = "client"

    email = db.Column(db.String)
    name = db.Column(db.String)
    address = db.Column(db.String)
    postcode = db.Column(db.String)

Answer 3

That particular exception is caused by describing the table more than once, either by repeatedly defining the class mapping (say, in the interactive interpreter, or in a function that can be called more than once), or by mixing declarative style class mappings with table reflection. In the former case, eliminate the repeated call; start a new interpreter if you are doing it interactively, or eliminate the extra function calls (possibly a good use for a singleton/borg object).

In the latter case, just do what the exception says, add __table_args__ = {'extend_existing': True} as an extra class variable in your class definitions. Only do this if you are actually sure that the table is being correctly described twice, as with table reflection.